CODEWITHSUNDEEP
sqlsql-serverdatetime
Kai
Kai

Reputation: 2168

Convert string formatted as yyyymmddhhnn to datetime

I have a table in an SQL Server database with a date field in it, presented as varchar in yyyymmddhhnn format (where nn is minutes). For example, 200012011200 would be 01 Dec 2000 12:00. I need to convert this to a datetime value, but none of the convert codes seems to cover it. It's closest to ISO format yyyymmdd but that doesn't include the time part, so calling convert(datetime, MyDateField, 112) fails.

The time part is important, so I can't just strip it off. How can I convert this to datetime?

Upvotes: 2

Views: 659

Answers (3)

MaVRoSCy
MaVRoSCy

Reputation: 17839

SELECT convert(varchar, cast(SUBSTRING('200012011200',1,4)+
'-'+SUBSTRING('200012011200',5,2)+
'-'+SUBSTRING('200012011200',7,2)+
' '+SUBSTRING('200012011200',9,2)+
':'+SUBSTRING('200012011200',11,2)+
':00'+
'.000' AS DATETIME), 109)

This will result in Dec 1 2000 12:00:00:000PM

Using the 112 as parameter will result to 20001201

Enjoy

UPDATE:

The convert(varchar...) is just for demonstration purposes. You can use this as well:

SELECT CAST(SUBSTRING('200012011200',1,4)+
'-'+SUBSTRING('200012011200',5,2)+
'-'+SUBSTRING('200012011200',7,2)+
' '+SUBSTRING('200012011200',9,2)+
':'+SUBSTRING('200012011200',11,2)+
':00'+
'.000' AS DATETIME)

Upvotes: 1

Kai
Kai

Reputation: 2168

My own rather brute-forced solution:

dateadd(mi, cast(substring(MyDateTime, 11, 2) as int), dateadd(hh, cast(substring(MyDateTime, 9, 2) as int), convert(datetime, left(MyDateTime, 8), 112)))

A horrendous contraption, but it does the job. Broken down from inside to out:

convert(datetime, left(MyDateTime, 8), 112)

Get the first 8 characters (yyyymmdd) and convert as ISO format.

dateadd(hh, cast(substring(MyDateTime, 9, 2) as int), ...)

Add on the hours with dateadd - characters 9 and 10, but needs to be cast and int before use in dateadd.

dateadd(mi, cast(substring(MyDateTime, 11, 2) as int), ...)

Add on the minutes with the same method, characters 11 and 12.

I can't imagine this solution could be any uglier without intentionally obfuscating it, and I doubt it's terribly efficient, but it does work.

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Try this

declare @t varchar(20)
set @t='200012011200'
select cast(stuff(stuff(@t, 11,0,':'),9,0,' ') as datetime)

Upvotes: 6

Related Questions