Converting Long to readable datetime problem

There is a MS-SQL 2005 server and contains a field called modtime that is
numeric(19)

select modtime from [usera].[dbo].[xxx]

and get the following output:

1225234572608

In JAVA, I put it into timestamp and it returns:

2008-10-29 06:56:12.608

However, when I run the following SQL, it returns different value.

select convert(datetime,(((1225234572608) / (1000.0 * 60.0 * 60.0 *
24.0)) ), 121)

1938-10-29 22:56:12.607

Both the date and tiume is different.

What is the proper way to convert the Long to Datetime in SQL statement?
Thanks.

Donald,

I believe that this is a matter of the 0 time. As you can see, your
minutes, second, milliseconds match up (with a 1 millisecond rounding
difference). So, the question is: What is the origin time that causes the
days and hours to be wrong? By getting the difference between your answers,
we find the origin date for your Java code is:

SELECT DATEADD(hour,datediff(hour,'1938-10-29 22:56:12.607','2008-10-29
06:56:12.608'),0)
-------------
1970-01-01 08:00:00.000

http://archives.postgresql.org/pgsql-bugs/2002-07/msg00117.php
This seems to show that one timezone dependent UNIX zero date (for US
Pacific time) is 1970-01-01 08:00:00.

However, SQL Server's zero date is 1900-01-01 00:00:00.000.

So, you are adding numbers to a different start point. For your case, after
casting to date time, you would need to add the hours from 1900-01-01
00:00:00.000 to 1970-01-01 08:00:00 (613616 hours) to your date. (You also
will need to decide how you will handle time zones, if that is an issue for
you.) This would look like this:

select dateadd(HOUR,613616, CAST(1225234572608.0 / (1000.0*60.0*60.0*24.0)
AS DATETIME))
-----------------------
2008-10-29 06:56:12.607

Time is not the simplest thing.

RLF

"DonaldFung" wrote in message

> There is a MS-SQL 2005 server and contains a field called modtime that is
> numeric(19)
>
> select modtime from [usera].[dbo].[xxx]
>
> and get the following output:
>
> 1225234572608
>
> In JAVA, I put it into timestamp and it returns:
>
> 2008-10-29 06:56:12.608
>
> However, when I run the following SQL, it returns different value.
>
> select convert(datetime,(((1225234572608) / (1000.0 * 60.0 * 60.0 *
> 24.0)) ), 121)
>
> 1938-10-29 22:56:12.607
>
> Both the date and tiume is different.
>
> What is the proper way to convert the Long to Datetime in SQL statement?
> Thanks.
>