How to convert string to datetime in SQL

Question

I have done my share in converting string to date in SQL Server 2016 but never seen a date with the format like the one below. I have looked online for a possible way to convert it without success. Any help helping me converting the string below to datetime datatype in SQL will be appreciated.

Fri Aug 24 2018 22:28:40

Regards,

I'm not sure that SQL Server can directly convert your datetime string. — Tim Biegeleisen, Aug 26 '18 at 03:12
Do not use logic in SQL... Delegate it to your Bussines Logic language... ASP? .NET? C#? — JuliSmz, Aug 26 '18 at 03:13
I am not familiar with any of those languages. I usually build all my logic in SQL — user2823833, Aug 26 '18 at 03:23
Usually logic and presentation formating goes into whatever client-side language you happen to use and are familiar with. Leave the DB engine the raw datetime for which it's optimized. — Alejandro, Aug 26 '18 at 03:37
You shouldn't be storing timestamps as strings to begin with — , Aug 26 '18 at 05:00
https://stackoverflow.com/questions/207190/sql-server-string-to-date-conversion — Y.S, Aug 26 '18 at 09:29

score 1 · Accepted Answer · edited Aug 26 '18 at 04:59

1

Found a way to do it in SQL Server:

select cast(right("string",20) as datetime) as dateColumn
from table

edited Aug 26 '18 at 04:59

answered Aug 26 '18 at 04:05

user2823833

95
2
9

Just to mention this: Be aware of the language dependency. "Aug" would work in many cases, but "Dec" was "Dez" in my culture... This might work in many cases and still breaks on another system. – Shnugo Aug 27 '18 at 11:04

score 0 · Answer 2 · answered Aug 26 '18 at 10:01

When dealing with non-standard date formats, I usually lookup CONVERT function in SQL Server, as it io very "elastic": you can provide various formats to it, so it recognizes them appropriately.

I use this site for reference. There you can find, that the closest date format is indicated by 100:

0   100    mon dd yyyy hh:miAM/PM    Default

It's your format except it doesn't include unnecessary in this case day of week information. So all you need to do is cut out the Fri part:

substring(myDatetimestring, 5, 1000)

or even better

substring(myDatetimeString, charindex(' ', myDatetimeString), 1000)

if the day of week part may have variable length. Don't worry about 1000, it only assures that we will have the rest of the string, not any less.

Now we have proper date format, since it says that it's default, we don't need to supply 100 to convert function and also we can use cast function instead, as you discovered, so we could do:

cast(substring(myDatetimeString, charindex(' ', myDatetimeString), 1000) as datetime)

or

convert(datetime, substring(myDatetimeString, charindex(' ', myDatetimeString), 1000), 100)

or

convert(datetime, substring(myDatetimeString, charindex(' ', myDatetimeString), 1000))

How to convert string to datetime in SQL

2 Answers2