CODEWITHSUNDEEP
sql-serverdatetimetype-conversionssmsvarchar
Anjali
Anjali

Reputation: 23

How to convert a varchar with month names into datetime in SQL Server

I have a flat file containing some data which I'm inserting into a table in SQL Server database.

One of the columns in the flat file (say time-stamp) is of the form : 09-OCT-17 08.22.04.000000 PM. I want to convert it to datetime format before/during insertion into the table.

Can anyone please tell me how this can be done?? Thanks!

flatfile input values are like 09-OCT-17 08.22.04.000000 PM, 09-OCT-17 09.31.36.000000 PM. Expected output is: 2017-10-09 08:22:04.000, 2017-10-09 09:31:36.000

Upvotes: 0

Views: 82

Answers (1)

Sean Lange
Sean Lange

Reputation: 33571

As stated this is a horrific format to start with but some basic string manipulation makes short work of this. Still not sure why 9pm should have 9 as the hour. In a proper datetime it would be 21. This works to get the correct hour. If you really want to always get the hour and ignore AM/PM you could add another replace to replace PM with AM.

declare @Something varchar(50) = '09-OCT-17 08.22.04.000000 PM'

select convert(datetime2, stuff(replace(@Something, '.', ':'), 20, 3, ''))

Upvotes: 2

Related Questions