Most of the times, Converting data in various other forms to DateTime type is headache in SQLserver. In this post, I’ll discuss how to convert these data types to ‘DateTime’.

Sometimes date values that come from disparate sources may be of a different data type such as int, varchar, string etc. In such cases we often need to convert this value back to DateTime data type.

Here in this post we will consider two most common scenarios of converting

Int to DateTime

Varchar datatype to DateTime

Int to DateTime

Lets assume that we have a date value along with time part which is stored in Bigint datatype. This can be done in SQL as-

declare @date bigint

set @date=20101219201119

The first 8 numbers denote a date and rest of numbers denote time in the format HHMMSS. This code can be converted to DateTime data type with following SQL statement-

select

cast(left(date,8)+”+stuff(stuff(substring(date,9,6),5,0,’:'),3,0,’:')

as datetime)

from

(select cast(@date as varchar(20)) as date) as t

In the above code, left(date,8) extracts the date value i.e. first 8 digits of ‘@date’ variable. In order to have a proper date format we need to add ‘:’ (colons) in each of the time parts (hour, minute and second). The stuff function is used to add ‘:’ (colons) in the 3rd and 5th position and the entire string is converted to DateTime.

Varchar to DateTime

Lets assume that we have a date value along with time part which is stored in Varchar datatype. This can be done in SQL as-

declare @date varchar(20)

set @date=’20101219 201119′

This ‘@date’ string can be converted to DateTime datatype using following SQL statement

select cast(left(@date,8)+ ‘ ‘ + stuff(stuff(substring(@date,10,6),5,0,’:'),3,0,’:')

as datetime)

Like this article? or have something to say? Note it down in your comments.