Jaron Cook Jaron Cook - 2 months ago 11
SQL Question

Convert varchar MMDDYYYY to MM/DD/YYYY datetime and select the most recent date only

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 as
MM/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.

Answer

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

SQLFiddle Demo

Comments