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;




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



Did you like this post?
kick it on DotNetKicks.com
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

 
  Feedback:

comments

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

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