Convert data stored as varchar in DD MMM YYYY format to DATETIME-CodePudding

I am trying to convert a value (varchar) in dd mmm yyyy format to a date using the below in SQL server.

SELECT convert(DATETIME, mydate, 106) FROM mytable

Getting the error:

Msg 241, Level 16, State 1, Line 28 Conversion failed when converting date and/or time from character string.

Can someone guide me.

CodePudding user response：

Use TRY_CONVERT to identify all bad records (and parse the ones that comply with your expected format).

Find the bad records

SELECT
  mydate
FROM mytable
WHERE TRY_CONVERT(datetime, mydate, 106) IS NULL

Convert the good ones

SELECT
  TRY_CONVERT(datetime, mydate, 106)
FROM mytable

CodePudding user response：

This will work without issue if the format is dd mmm yyyy, if your data is formatted differently for any of the records then this will be the likely cause of your error.

Make sure all entries conform to the stated format and transform any entries which do not.