March 10, 2009

Convert Integer to Date and Time in SQL Server

One of my users recently had a requirement where he had a field in the database that stored datetime as integers. The integers represented a date calculated keeping '1/1/2000 ' as the base date. He wanted to convert that integer back into date, however keeping '1/1/2000 00:00:00' as the base date. Here's how the requirement was achieved:

DROP Table #SampleTable

CREATE Table #SampleTable (

[numasdate] int



Insert Into #SampleTable Values('99933436');

Insert Into #SampleTable Values('55232337');

Insert Into #SampleTable Values('92323323');

Insert Into #SampleTable Values('111222525');


SELECT dateadd(ss,[numasdate],'1/1/2000') as ConvertToDate

FROM #SampleTable;


2003-03-02 15:17:16.000

2001-10-01 06:18:57.000

2002-12-04 13:22:03.000

2003-07-11 07:08:45.000

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

About The Author

Suprotim Agarwal, ASP.NET Architecture MVP works as an Architect Consultant and provides consultancy on how to design and develop Web applications.

Suprotim is also the founder and primary contributor to DevCurry, DotNetCurry and SQLServerCurry. He has also written an EBook 51 Recipes using jQuery with ASP.NET Controls.

Follow him on twitter @suprotimagarwal



0 Responses to "Convert Integer to Date and Time in SQL Server"

Copyright © 2009-2014 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions