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


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s