Sql Server Administrators often face a problem when working with some application, in which their developers decided to store dates as integers. In those applications the dates are stored as integers in format YYYYMMDD. This post will illustrate how to convert YYYYMMDD format to smalldatetime data type and back on Microsoft Sql Server.

Let's start from creating a table which will store date as integer in YYYYMMDD format

create table t1( 

    c1 int)



insert into t1(c1) values(20090620)

(1 row(s) affected)

select * from t1


20090620

To convert integer to smalldatetime, we will use CAST function. First we will convert integer to varchar and then to smalldatetime.

select cast(cast(c1 as varchar(10)) as smalldatetime) 

from t1



2009-06-20 00:00:00

To convert a smalldatetime date to integer we can use CONVERT and CAST functions.

create table t2( 

c1 smalldatetime)



insert into t2(c1) values(getdate())

select cast(convert(varchar(8),c1,112) as integer) 

from t2


20090630