How to convert dates from varchar to datetime2 in SQL Server?

1
votes

How can I convert these kind of date values from varchar to datetime2?

WITH dates AS (
SELECT '6.7.2012' AS dtm
UNION 
SELECT '13.2.2012' AS dtm
UNION
SELECT '3.12.2012' AS dtm
UNION 
SELECT '20.11.2012' AS dtm
)
SELECT CAST(dtm as datetime2) FROM dates
;

This results in an error:

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

1
sqlsql-serverdatesql-server-2016

1 Answers

9
votes

Use try_convert instead of cast:

WITH dates AS (
SELECT '6.7.2012' AS dtm
UNION 
SELECT '13.2.2012' AS dtm
UNION
SELECT '3.12.2012' AS dtm
UNION 
SELECT '20.11.2012' AS dtm
)
SELECT TRY_CONVERT(datetime2, dtm, 104) FROM dates
;

Results:

13.02.2012 00:00:00
20.11.2012 00:00:00
03.12.2012 00:00:00
06.07.2012 00:00:00

Note: try_convert was introduced in 2012 version, for earlier versions you need to use convert, risking an exception if the varchar value can't be converted using the specified style.