GETDATE(),SYSDATETIME(),GETUTCDATE() and SYSUTCDATETIME() functions use the windows API GetSystemTimeAsFileTime() to fetch the current data time. The precision for this API is fixed to ~100 Nano seconds.
Date time Functions SYSDATETIME (), SYSUTCDATETIME() are high precition functions and the functions GETDATE(), GETUTCDATE() are low precision functions.
Returns the current database system timestamp as a datetime value without the database time zone offset. This value is derived from the operating system of the computer on which the instance of SQL Server is running.
Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is not included.
Returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time). This value is derived from the operating system of the computer on which the instance of SQL Server is running.
Returns a datetime2 value that contains the date and time of the computer on which the instance of SQL Server is running. The date and time is returned as UTC time (Coordinated Universal Time). The fractional second precision specification has a range from 1 to 7 digits. The default precision is 7 digits.
SELECT 'GETDATE' AS FunctionName, GETDATE() AS DateTimeFormat
SELECT 'SYSDATETIME' , SYSDATETIME()
SELECT 'GETUTCDATE', GETUTCDATE()
SELECT 'SYSUTCDATETIME', SYSUTCDATETIME()
GETDATE( ) and SELECT SYSDATETIME( ) both return the current date and time in your time zone. However, GETDATE( ) shows fractional seconds expressed in milliseconds (.333 second), and SYSDATETIME( ) shows fractional seconds expressed in nanoseconds (.3333333 second). SYSDATETIME( ) return similar results but their precisions differ.
What time is it right now in the UK? UTC is Coordinated Universal Time, formerly known as Greenwich Mean Time (GMT). (UTC is also known by the terms zulu time, world time, and universal time.)
SELECT GETUTCDATE( ) will show the current time expressed in terms of UTC. GETUTCDATE( ) is less precise than SYSUTCDATETIME ( ). However there is a UTC function that gets down to the nanoseconds, SYSUTCDATETIME( ). When we run all 4 of these functions together. We see the two top times in my local time zone (in my case the Pacific time zone) and the two bottom times in UTC.
The difference between GETDATE() and GETUTCDATE() is time zone number of the SQL Server machine. The example below gives you a good example:
DECLARE @local_time DATETIME;
DECLARE @gmt_time DATETIME;
SET @local_time = GETDATE();
SET @gmt_time = GETUTCDATE();
SELECT 'Server local time: '
SELECT 'Server GMT time: '
SELECT 'Server time zone: '
--Server local time: Jan 27 2012 10:59PM
--Server GMT time: Jan 27 2012 5:29PM
--Server time zone: 5
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.
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.
“GETUTCDATE()” which is the current UTC time which does not vary by time of year or timezone. Therefore, all the time was based off the same timezone and it never changed for daylight savings.
Using GETUTCDATE instead of GETDATE was a simple solution to a complicated problem to solve timezones and daylight savings changes.
How to convert local DateTime to UTC DateTime?
SELECT DATEADD(hh, DATEDIFF(hh, GETDATE(), GETUTCDATE()), GETDATE()) AS 'UTC DateTime' ,
GETDATE() AS 'Local DateTime'