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

Question

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

The source of your problems: "I have a column of dates in varchar(50) ". — Zohar Peled, Jul 24 '18 at 10:05
Are you sure it's `dd/mm` and not `mm/dd`? What about `04/07` ? The only solution is to *replace* that column with a proper date type. Otherwise you'll always have to deal with conversion errors or worse, wrong dates — Panagiotis Kanavos, Jul 24 '18 at 11:34
The only thing worse than an error on production is wrong data on production... — Zohar Peled, Jul 24 '18 at 19:23

score 5 · Accepted Answer · answered Jul 24 '18 at 09:42

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

score 1 · Answer 2 · answered Jul 24 '18 at 09:47

convert(datetime, @dt, 103)

Check the results for all your provided values.

declare @dt varchar(50)='15/06/2017 00:00:00'
set @dt='03/04/2017 00:00:00'
select convert(datetime, @dt, 103)
set @dt='03/04/2017 00:00:00'
select convert(datetime, @dt, 103)
set @dt='15/06/2017 00:00:00'
select convert(datetime, @dt, 103)
set @dt='15/06/2017 00:00:00'
select convert(datetime, @dt, 103)
set @dt='17/05/2017 00:00:00'
select convert(datetime, @dt, 103)

score 1 · Answer 3 · answered Jul 24 '18 at 09:48

1

It is issue of DateFormat. You can easily judge that when Month is 15 it is giving error.

declare @st as varchar(50)='15/06/2017 00:00:00'
select Convert(DateTime2,@ST,103)

you can use the above code

answered Jul 24 '18 at 09:48

Deepak Kumar

648
6
14

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

3 Answers3