Link to home
Start Free TrialLog in
Avatar of fylix0000
fylix0000

asked on

How to get EST timestamp from this statement in SQL 2000?

select DATEDIFF( ss , '1970-01-01 00:00:00',getdate())

I beleive this returns the number of seconds just like the function :  select UNIX_TIMESTAMP() in MySQL.

However I noticed that the DATEDIFF one return the number of seconds that is 4 hours ahead since I am at EST time zone, my guess is it is default to the London time.  I can easily fix this with a hack by minus 14400 seconds which represent 4 hours.  However, my concern is the time change we have in America such as 1 hour shorter, 1 hour longer in fall and spring..... I could not find a way to standardize this,  if you aware of the fix for this, please point me toward the right solution.


Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Aneesh
Aneesh
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of fylix0000
fylix0000

ASKER

Unfortunately I am using SQL 2000

Perhap this is something at my end? since I tried "select GETUTCDATE()" and I get 2006-05-17 16:13:17.843
 but my system time says 12:14PM.
Where is the sql server installed ? is it on the same machine ?
Reember, GETDATE() will return the time of your machine where SQL server is installed
Yes, I run it directly on my machine which I have the sql 2000 installed.
Strange this is I run this two query in the query window:


select  GETUTCDATE()

select GETDATE()

and I get

2006-05-17 16:33:18.560
 

2006-05-17 12:33:18.560   --> right time.
Check the windows time ? It will be the same as GETDATE()
Actually I got it, sorry for my latest silly comment.
So basically i use select DATEDIFF( ss , '1970-01-01 00:00:00',GETUTCDATE()) and get what I wanted.