Search

Tuesday, August 9, 2011

GetUTCDate

SELECT GETUTCDATE()
Returns the datetime value representing the current UTC time (Universal Time Coordinate 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 SQL Server is running.
And that is all, those are the ten functions that you should be using but currently you are not using all of them. Look them up in Books On Line so that you can see some more examples.


The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine. Note that the local time is from SQL server machine, not your local machine if you are connected to the server remotely. The local time is also adjusted by day-light savings.


It mean
GETDATE = Returns the day of the month for the specified date according to local time.
GETUTCDATE() = Returns the day (date) of the month in the specified date according to universal time.



Using GETUTCDATE instead of GETDATE was a simple solution to a complicated problem to solve timezones and daylight savings changes.
Example:
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = GETDATE();
SET @gmt_time = GETUTCDATE();
SELECT 'Server local time: '
   + CONVERT(VARCHAR(40),@local_time);
SELECT 'Server GMT time: '
   + CONVERT(VARCHAR(40),@gmt_time);
SELECT 'Server time zone: '
   + CONVERT(VARCHAR(40),
      DATEDIFF(hour,@gmt_time,@local_time));
GO
Output:
Server local time: Apr 26 2011 09:47PM
Server GMT time: Apr 26 2011  04:17PM
Server time zone: 5

No comments:

Post a Comment