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;

Output

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, MCSD, MCAD, MCDBA, MCSE, is the founder of DotNetCurry, DNC Magazine for Developers, SQLServerCurry and DevCurry. He has also authored a couple of books 51 Recipes using jQuery with ASP.NET Controls and a new one recently at The Absolutely Awesome jQuery CookBook.

Suprotim has received the prestigous Microsoft MVP award for nine times in a row now. In a professional capacity, he is the CEO of A2Z Knowledge Visuals Pvt Ltd, a digital group that represents premium web sites and digital publications comprising of Professional web, windows, mobile and cloud developers, technical managers, and architects.

Get in touch with him on Twitter @suprotimagarwal, LinkedIn or befriend him on Facebook