SQL – Converting an integer to datetime

Sometimes in life you come across integers that represent a datetime value and you want to convert it to make it more appealing . Here is a handy little helper that will convert integers represented as ‘YYYYMMDDHHMM’, ie 200805051010;

 select CAST(STUFF(STUFF(STUFF(STUFF(CAST(200805051010 as varchar(20) ), 5, 0, ‘-‘), 8, 0, ‘-‘),11,0,’ ‘), 14, 0, ‘:’) as datetime)
as dt.

(You could also use the convert function if you wanted a different date format, I know I could have cast the int to a varchar(8) and gotten a valid date without all the stuffing, but I needed to keep the time part of the datetime)

As with just about everything that I do, I am sure there is a much better more efficient way to do it that involves less typing, but it works and that is my ultimate goal

Advertisements