Converting varchar to date using cast

user_6002118 0
Viewed 89

I want to convert some "dates" to actual dates. This seems to work where the day is less than 13th. It seems to be recognising the 13th as a month.

SELECT [User ID],
       try_CAST([First Seen (BST)] as datetime) as [New First Seen Date]
       [First Seen (BST)],
       [Signed up (BST)]
FROM [dbo].[fix]

Shows as NULL when i would expect to convert these in the same format / type.

New First Seen Date                      First Seen Date
2018-11-08 23:38:00.000                  11/08/2018 23:38
NULL                                     20/08/2018 22:07
NULL                                     26/08/2018 22:41
2018-09-09 20:55:00.000                  09/09/2018 20:55
2018-09-09 23:24:00.000                  09/09/2018 23:24

much appreciated

date sql-server sql
3 Answers

Since your date strings are DD/MM/YYYY you can use style 103.

select NewDate = TRY_CONVERT(datetime, s.FirstSeenDate, 103)
    , s.FirstSeenDate
from 
(values
    ('11/08/2018 23:38')
    , ('20/08/2018 22:07')
    , ('26/08/2018 22:41')
    , ('09/09/2018 20:55')
    , ('09/09/2018 23:24')
)s(FirstSeenDate)
user_38131160

One relatively simple method is to break the values into two parts, one for the date and the other for the time portion:

select try_convert(datetime, left(str, 10), 104) + try_convert(datetime, right(str, 5))
from (values ('26/08/2018 22:41')) v(str);
user_11440350

I think it's a bit safer to specify date format for conversion - look at TRY_CONVERT (Transact-SQL) where you can set the format (see different formats here - CAST and CONVERT (Transact-SQL). I guess currently your dates are being treated as mm/dd/yyyy instead of dd/mm/yyyy, so you can use format 103 to conver them.

select
    try_convert(datetime, [First Seen Date], 103) as res
from ...

res
-------------------
11/08/2018 23:38:00
20/08/2018 22:07:00
26/08/2018 22:41:00
09/09/2018 20:55:00
09/09/2018 23:24:00

db<>fiddle demo

user_17448340
Related