Returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
 Note |
|---|
SYSDATETIME and SYSUTCDATETIME have more fractional seconds precision than GETDATE and GETUTCDATE. SYSDATETIMEOFFSET includes the system time zone offset. SYSDATETIME, SYSUTCDATETIME, and SYSDATETIMEOFFSET can be assigned to a variable of any of the date and time types. |
For an overview of all Transact-SQL date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL). For information and examples that are common to date and time data types and functions, see Using Date and Time Data.
SYSDATETIME ()
datetime2(7)
Transact-SQL statements can refer to SYSDATETIME anywhere they can refer to a datetime2(7) expression.
SYSDATETIME is a nondeterministic function. Views and expressions that reference this function in a column cannot be indexed.
|
Note
|
|---|
|
SQL Server 2008 obtains the date and time values by using the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API. |
The following examples use the six SQL Server system functions that return current date and time to return the date, time or both. The values are returned in series; therefore, their fractional seconds might be different.
A. Getting the current system date and time
SELECT SYSDATETIME()
,SYSDATETIMEOFFSET()
,SYSUTCDATETIME()
,CURRENT_TIMESTAMP
,GETDATE()
,GETUTCDATE();
/* Returned:
SYSDATETIME() 2007-04-30 13:10:02.0474381
SYSDATETIMEOFFSET()2007-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2007-04-30 20:10:02.0474381
CURRENT_TIMESTAMP 2007-04-30 13:10:02.047
GETDATE() 2007-04-30 13:10:02.047
GETUTCDATE() 2007-04-30 20:10:02.047
B. Getting the current system date
SELECT CONVERT (date, SYSDATETIME())
,CONVERT (date, SYSDATETIMEOFFSET())
,CONVERT (date, SYSUTCDATETIME())
,CONVERT (date, CURRENT_TIMESTAMP)
,CONVERT (date, GETDATE())
,CONVERT (date, GETUTCDATE());
/* All returned 2007-04-30 */
C. Getting the current system time
SELECT CONVERT (time, SYSDATETIME())
,CONVERT (time, SYSDATETIMEOFFSET())
,CONVERT (time, SYSUTCDATETIME())
,CONVERT (time, CURRENT_TIMESTAMP)
,CONVERT (time, GETDATE())
,CONVERT (time, GETUTCDATE());
/* Returned
SYSDATETIME() 13:18:45.3490361
SYSDATETIMEOFFSET()13:18:45.3490361
SYSUTCDATETIME() 20:18:45.3490361
CURRENT_TIMESTAMP 13:18:45.3470000
GETDATE() 13:18:45.3470000
GETUTCDATE() 20:18:45.3470000
*/
Reference
Microsoft marketing team, please NOTE: When a customer ALREADY has your current product and are attempting to get valuable information regarding it listing functions that are available only in the next version IS NOT GOOD MARKETING.
Instead of feeling good about the product I have and feeling better about the new product I feel angry at the company who produces the software and have to resist feeling resentment that money was paid for the current version and will likely be paid for the future product. [Tai Yee - MSFT] Thank you for your feedback on this documentation topic. I have submitted it to the team that writes this documentation set. FYI, the best way to submit feedback is not the Community Content section, but to use the Send Feedback controls that are available. For more information, please see my blog post: http://blogs.msdn.com/sqlserverue/archive/2007/09/21/please-give-feedback-on-documentation.aspx