• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 283
  • Last Modified:

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.
0
fylix0000
Asked:
fylix0000
  • 5
  • 3
  • 2
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
Hi fylix0000,
Getdate () - will return the Server time (Server in which SQL is installed )
There is another similar function , which returns the UTC

GETUTCDATE()


Aneesh R!
0
 
LowfatspreadCommented:
which version of SQL server are you running?

do you also have the

GetUTCDATE function
which i assume function similar to the UNIX_TIMESTAMP() in MYSQL

from   BOL for SQL 2005

Returns the datetime value representing the current UTC time (Coordinated Universal Time or Greenwich Mean Time). The current UTC time is derived from the current local time and the time zone setting in the operating system of the computer on which the instance of Microsoft SQL Server is running.

0
 
fylix0000Author Commented:
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.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Aneesh RetnakaranDatabase AdministratorCommented:
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
0
 
fylix0000Author Commented:
Yes, I run it directly on my machine which I have the sql 2000 installed.
0
 
fylix0000Author Commented:
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.
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Check the windows time ? It will be the same as GETDATE()
0
 
fylix0000Author Commented:
Actually I got it, sorry for my latest silly comment.
0
 
fylix0000Author Commented:
So basically i use select DATEDIFF( ss , '1970-01-01 00:00:00',GETUTCDATE()) and get what I wanted.
0
 
LowfatspreadCommented:
great
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

  • 5
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now