SQL convert String date of style d-MMM-yyyy to DATE-CodePudding

I'm importing a CSV file and one of the fields is a non-standard date string with entries like 7-Dec-2021

Any ideas for to convert this into a DATETIME object that I can insert into my SQL table? Standard CAST/CONVERT didn't work.

Microsoft SQL Server 2012 - 11.0.2100.60

CodePudding user response：

TRY_CONVERT seems to be working here:

SELECT TRY_CONVERT(datetime, '7-Dec-2021');  -- 2021-12-07 00:00:00.000

Edit:

Your input date almost matches format mask 106, once we replace the dashes with spaces. Consider this solution:

SELECT dt, CONVERT(datetime, REPLACE(dt, '-', ' '), 106) AS dt_out
FROM yourTable;

This outputs 2021-12-07 00:00:00.000 for dt_out on SQL Server 2014, and should behave the same way on SQL Server 2012.

CodePudding user response：

I am guessing that you are using a LOGIN where its language isn't English based, and as a result 'Dec' (and/or other months) isn't recognised as a valid month name.

You can specify your language to be used for the batch and then CONVERT:

SET LANGUAGE BRITISH;

SELECT TRY_CONVERT(date,'7-Dec-2021',106);