Convert Varchar Mmddyyyy to Mm/Dd/Yyyy Datetime and Select the Most Recent Date Only

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

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

SQL how to convert MM-DD-YYYY string value to date

I think I figured it out:

cast(to_date((FROM_UNIXTIME(UNIX_TIMESTAMP(value, "MM-dd-yyyy HH:mm:ss:SSS"), "yyyy-MM-dd HH:mm:ss.SSS"))) as date) as date1

How to handle MMddyyyy date format in SQL Server?

There is no style code for MMddyyyy as far as I am aware, so the easiest way is probably to just convert the string to yyyyMMdd first, e.g.

CONCAT(RIGHT(@dt, 4), LEFT(@dt, 4))

Then convert that to a date:

DECLARE @dt varchar(8)= '02022020';
SELECT CONVERT(DATE, CONCAT(RIGHT(@dt, 4), LEFT(@dt, 4)), 112)

How to convert a "dd/mm/yyyy" string to datetime in SQL Server?

The last argument of CONVERT seems to determine the format used for parsing. Consult MSDN docs for CONVERT.

111 - the one you are using is Japan yy/mm/dd.

I guess the one you are looking for is 103, that is dd/mm/yyyy.

So you should try:

 SELECT convert(datetime, '23/07/2009', 103)

---

Related Topics