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