Join Us!

Spaces are allowed; punctuation is not allowed except for periods, hyphens, and underscores.
A valid e-mail address. All e-mails from the system will be sent to this address. The e-mail address is not made public and will only be used if you wish to receive a new password or wish to receive certain news or notifications by e-mail.
CAPTCHA
This question is for testing whether you are a human visitor and to prevent automated spam submissions.
Image CAPTCHA
Enter the characters shown in the image.

Login

Enter your Project Envision username.
Enter the password that accompanies your username.
Request new password

Sql Server Convert YYYYMMDD to Date

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