Convert varchar MMDDYYYY to MM/DD/YYYY datetime and select the most recent date only
Solution 1
This question is for almost a year ago, nut probably someone can find it useful.
You need to CONVERT
your string to DATE
format and use a ROW_NUMBER
function to window your result set.
Create table
DECLARE @tbl TABLE(Id INT, myDate VARCHAR(8))
Sample data
INSERT @tbl
SELECT 1 , '01302014' UNION ALL
SELECT 1 , '04222014' UNION ALL
SELECT 2 , '01302014'
Query
;WITH C AS(
SELECT ROW_NUMBER() OVER (PARTITION BY Id ORDER BY CONVERT(DATETIME, (SUBSTRING(myDate, 5, 4) + '.' + SUBSTRING(myDate, 1, 2) + '.' + SUBSTRING(myDate, 3, 2)), 101) DESC) AS Rn
,Id
,CAST(CONVERT(DATETIME, (SUBSTRING(myDate, 5, 4) + '.' + SUBSTRING(myDate, 1, 2) + '.' + SUBSTRING(myDate, 3, 2)), 101) AS DATE) AS myDate
FROM @tbl
)
SELECT Id, myDate
FROM C
WHERE Rn = 1
Solution 2
Using a CONVERT like the following code snippet will work on any SQL Server regardless of language and/or locale configuration.
DECLARE @OldDate varchar(8);
SELECT @OldDate = '04252012';
SELECT CONVERT(datetime, substring(@OldDate,5,4) + '-' + substring(@OldDate,1,2) + '-' + substring(@OldDate,3,2) + 'T00:00:00')
Jaron Cook
Updated on June 22, 2020Comments
-
Jaron Cook almost 4 years
Okay, so I have kind of a weird issue... the dates in the table have been entered in as string values
MMDDYYYY
and I'm trying to have the displayed asMM/DD/YYYY
in a report and only select the most recent date pertaining to an ID, because some ID's may have multiple dates.Example of my table:
ID | MyDate | ------+----------+ 1 | 01302014 | 1 | 04222014 | 2 | 01302014 |
What I want to see when I select and insert into a temp table is this:
ID | MyDate | ------+-----------+ 1 | 4/22/2014 | 2 | 1/30/2014 |
I know that storing dates as string values is a poor practice especially when storing them as
MMDDYYYY
, but does anyone have a solution to this nightmare?EDIT
I forgot to mention that some fields might be NULL. Not sure if that makes a difference or not, but I think it does if I try to flip the dates using Right, Left, Convert.