CODEWITHSUNDEEP
sql-serverdatesql-server-2012
Tamati
Tamati

Reputation: 9

Convert and sort varchar Date dd-MMM-yyyy

I'm using a table that has the date as varchar. example dateformat: 17-Jun-2015 I have tried the following ways to convert and sort the date(dd-MMM-yyyy) to dateTime.

SELECT date, name, author
FROM sometable
ORDER BY CONVERT(DATETIME, date, 106) DESC

I have also tried converting the date in the select statement. doesn't work. The error is

Conversion failed when converting date and/or time from character string.

The conversion types through some similar questions but I have not found any solutions to the format I have. Is there some way of selecting the delimiter between the day month and year??

I also had a browse through this link which has the formats for datetime formats. 106 was the closest to my varchar date. Only my date in the table has '-' between day month and year.

https://msdn.microsoft.com/en-us/library/ms187928.aspx

Appreciate any help.

Upvotes: 1

Views: 3116

Answers (2)

FutbolFan
FutbolFan

Reputation: 13733

I would use date style 113 to convert to datetime format like this:

DECLARE @date VARCHAR(20) = '17-Jun-2015';

SELECT CONVERT(VARCHAR(20),CAST(@date AS DATETIME),113)

Upvotes: 0

Eric
Eric

Reputation: 5743

It should be absolutely fine to use 106 to convert your date format.

But I guess your table contains some of the invalid values in the column causes the error, try to spot them out by TRY_CONVERT:

SELECT date, name, author, TRY_CONVERT(datetime, date, 106) AS convertresult
FROM sometable
WHERE TRY_CONVERT(datetime, date, 106) IS NULL AND date IS NOT NULL

Upvotes: 2

Related Questions