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

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

No comments: