converting datetime int

[js]

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.

select iorg_name as org, ref_num as [ticketnum], c_first_name as
[firstname], c_last_name as [lastname], sym as type, [description] as
summary, status, dateadd(s,open_date,'12/31/1969 08:00:00pm') as
opened, dateadd(s,last_mod_dt,'12/31/1969 08:00:00pm') as irt,
dateadd(s,close_date,'12/31/1969 08:00:00pm') as closed from
AHD.dbo.HDreports reportview WHERE reportview.open_date >= 1080882000
AND reportview.open_date <= 1081227599.

The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?

[Erland Sommarskog]

js (an********@yahoo.com) writes:

I have tables with columns that stores datetime data in int format on
SQL server 2000. For example, the datetime for '4/5/2004
00:00:00.000am' is stored as 1081180800. "4/4/2004 11:59:59.000pm' is
1081180799. I need to generate reports that display datetime columns
in "mm/dd/yyyy hh:mn:ss" format with am or pm at the end. Bellow is
my query statment.
...
The result shows correctly with those records that are in daylight
saving time. Those records in standard time show 1 hour behind.

Does anyone know how to make this query correctly display the data in
properly?

That was a very odd way of storing dates, and probably not the best one.
Apparently this is some variation of Unix, where time is counted as number
of seconds since 1970-01-01 00:00:00, except that here the staring point
is 1969-12-30 20:00:00.

SQL Server is not timezone aware, so you should not expect to be able
to get fully accurate results. You are probably best of getting the
integer value to the client, and try the Windows functions for date
and time. They are likely to work out better.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp