CODEWITHSUNDEEP
sql-server
TomNash
TomNash

Reputation: 3288

Convert datetime with AM/PM to standard SQL server datetime

I have a date in YYYYMMDDHHTT time format, how can I convert this to a 24-hour datetime (i.e. SQL server DATETIME)

Example: 201710031035AM

Desired: 2017-10-03 10:35:00.000

Upvotes: 0

Views: 486

Answers (1)

zambonee
zambonee

Reputation: 1647

I would split up and concatenate the old string format so that SQL can recognize it as a date string

SELECT CONVERT(datetime, CONCAT(
    SUBSTRING(oldFormat, 5, 2), ' ', 
    SUBSTRING(oldFormat, 7, 2), ' ', 
    SUBSTRING(oldFormat, 1, 4), ' ',
    SUBSTRING(oldFormat, 9, 2), ':',
    SUBSTRING(oldFormat, 11, 4)
), 100)

Where 100 is the datetime format 'mon dd yyyy hh:miAM (or PM)'. Or you can split up the datetime parts, and then pass then through the DATETIMEFROMPARTS function as ints. Just make sure that all of the old values are consistently formated first!

Upvotes: 1

Related Questions