Search code examples
sqlsql-serverdatedatetime2

Convert varchar "dd/mm/yyyy hh:mm:ss" to datetime2

I have a column of dates in varchar(50) format written as "dd/mm/yyyy" I need to convert it to datetime2 format however when it runs it assumes that the format is "mm/dd/yyyy" and converts accordingly, throwing an error when the day field exceeds 12. How do I make it pull the data in the correct way?

e.g.

03/04/2017 00:00:00
03/04/2017 00:00:00
15/06/2017 00:00:00
15/06/2017 00:00:00
17/05/2017 00:00:00

with the last 3 throwing errors.

Current command:

select case when try_convert(datetime2,[Date]) is not null
    then cast([Date]as datetime2)
    else null
    end, [Date]
from [Source1]

Results:

2017-03-04 00:00:00.0000000  03/04/2017 00:00:00
2017-03-04 00:00:00.0000000  03/04/2017 00:00:00
NULL                         15/06/2017 00:00:00
NULL                         15/06/2017 00:00:00
NULL                         17/05/2017 00:00:00
Solution

  • Try this instead:

    WITH VTE AS (
    SELECT *
    FROM  (VALUES ('03/04/2017 00:00:00'),
                  ('03/04/2017 00:00:00'),
                  ('15/06/2017 00:00:00'),
                  ('15/06/2017 00:00:00'),
                  ('17/05/2017 00:00:00')) V(StringDatetime))
SELECT *,
       TRY_CONVERT(datetime2(0), VTE.StringDatetime, 103) AS NonStringDatetime2
FROM VTE;

    You can find a list of style codes in the documentation: Date and Time Styles