SQL server - converting string to datetime

Question

I need to convert a column that contains dates and times in the format 08-JAN-19 09.35.58.173000000 AM to datetime. I've included the code I've tried below - the code commented out is currently not working.

SELECT [last_updated_at]
    , SUBSTRING([last_updated_at], 1, 9)
    , convert(datetime, SUBSTRING([last_updated_at], 1, 9), 103) as Date
    , SUBSTRING([last_updated_at], 11, 18)
    --, convert(datetime, SUBSTRING([last_updated_at], 11, 18), 103) as Time --This fails
    --, convert(datetime, SUBSTRING([last_updated_at], 1, 9), 103) + convert(datetime, SUBSTRING([last_updated_at], 11, 18), 103) as DateTime --final output datetime column
FROM #temp_dates

I can, but not sure why that would be required? I tried the below solution from @Vlam and it worked! — Chipmunk_da
– Chipmunk_da, Commented May 4, 2021 at 14:09
The root cause of the issue is the schema is wrong. If you can change the schema I suggest you do so. — Keith John Hutchison
– Keith John Hutchison, Commented May 4, 2021 at 22:04

Alan Burstein · Accepted Answer · 2021-05-04 13:41:46Z

2

The only problem with your data is that you need the time portion of your date to look like this: 2019-01-08 09:35:58.173 instead of 2019-01-08 09.35.58.173 (the dots after the hour and minute need to be colons instead of dots.

--==== Original formatting
DECLARE @date VARCHAR(20) = '08-JAN-19 09.35.58.173000000'

--==== Solution
SELECT CAST(STUFF(STUFF(@date,13,1,':'),16,1,':') AS DATETIME);

Note that this truncates (not rounds) to the nearest millisecond so, 09:35:58.173 becomes 09:35:58.100. If milliseconds are an issue then a bit more finagling will be required.

answered May 4, 2021 at 13:41

Alan Burstein

8,0061 gold badge18 silver badges19 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Vlam · Accepted Answer · 2021-05-04 12:42:41Z

1

Please try the below:

select convert(datetime, substring(replace(replace('08-JAN-19 09.35.58.173000000', '-', ' '), '.', ':'), 1, 18))

Before you can convert a string to datetime, your string must conform to certain patterns as shown in https://learn.microsoft.com/en-us/sql/t-sql/data-types/datetime-transact-sql?view=sql-server-ver15

answered May 4, 2021 at 12:42

Vlam

1,7901 gold badge10 silver badges21 bronze badges

Collectives™ on Stack Overflow

SQL server - converting string to datetime

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related