Return System Date and Time values
Overview
SQL derives all system date and time values from the operating system of the computer on which the instance of SQL Server runs.
SQL Server 2019 (15.x) derives the date and time values through use of the GetSystemTimeAsFileTime() Windows API. The accuracy depends on the computer hardware and version of Windows on which the instance of SQL Server running. This API has a precision fixed at 100 nanoseconds. Use the GetSystemTimeAdjustment() Windows API to determine the accuracy. The return system date and time value functions covered in this section are:
SYSDATETIME
SYSDATETIME returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
Syntax
SYSDATETIME ( )
Return Types
datetime2(7)
Remarks
SQL statements can refer to SYSDATETIME
anywhere they can refer to a datetime2(7) expression.
SYSDATETIME
is a nondeterministic function. You can't index views and expressions that reference this function.
Example 1: Get the current System Date and Time
SELECT
SYSDATETIME(),
SYSDATETIMEOFFSET(),
SYSUTCDATETIME(),
GETDATE(),
GETUTCDATE()
/*
Returned:
SYSDATETIME() 2020-04-30 13:10:02.0474381
SYSDATETIMEOFFSET() 2020-04-30 13:10:02.0474381 -07:00
SYSUTCDATETIME() 2020-04-30 20:10:02.0474381
GETDATE() 2020-04-30 13:10:02.047
GETUTCDATE() 2020-04-30 20:10:02.047
*/
Example 2: Get the current System Date
SELECT
CONVERT(date, SYSDATETIME()),
CONVERT(date, SYSDATETIMEOFFSET()),
CONVERT(date, SYSUTCDATETIME()),
CONVERT(date, GETDATE()),
CONVERT(date, GETUTCDATE())
/* All returned 2020-04-30 */
Example 3: Get the current System Time
SELECT
CONVERT(time, SYSDATETIME()),
CONVERT(time, SYSDATETIMEOFFSET()),
CONVERT(time, SYSUTCDATETIME()),
CONVERT(time, GETDATE()),
CONVERT(time, GETUTCDATE())
/*
Returned:
SYSDATETIME() 13:18:45.3490361
SYSDATETIMEOFFSET() 13:18:45.3490361
SYSUTCDATETIME() 20:18:45.3490361
GETDATE() 13:18:45.3470000
GETUTCDATE() 20:18:45.3470000
*/
SYSDATETIMEOFFSET
SYSDATETIMEOFFSET returns a datetimeoffset(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 included.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
Syntax
SYSDATETIMEOFFSET()
Return Types
datetimeoffset(7)
Remarks
SQL statements can refer to SYSDATETIMEOFFSET
anywhere they can refer to a datetimeoffset expression.
SYSDATETIMEOFFSET
is a nondeterministic function. You can't index views and expressions that reference this function.
Example 1: Show formats Returned by the Date and Time Functions
SELECT
SYSDATETIME() AS [SYSDATETIME()],
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
SYSUTCDATETIME() AS [SYSUTCDATETIME()],
GETDATE() AS [GETDATE()],
GETUTCDATE() AS [GETUTCDATE()]
Example 2: Convert Date and Time to Date
SELECT
CONVERT(date, SYSDATETIME()),
CONVERT(date, SYSDATETIMEOFFSET()),
CONVERT(date, SYSUTCDATETIME()),
CONVERT(date, GETDATE()),
CONVERT(date, GETUTCDATE())
Example 3: Convert Date and Time to Times
SELECT
CONVERT(time, SYSDATETIME()) AS [SYSDATETIME()],
CONVERT(time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()],
CONVERT(time, SYSUTCDATETIME()) AS [SYSUTCDATETIME()],
CONVERT(time, GETDATE()) AS [GETDATE()],
CONVERT(time, GETUTCDATE()) AS [GETUTCDATE()]
SYSUTCDATETIME
SYSUTCDATETIME
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 are 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.
This function isn't currently supported in PostgreSQL deployments of the Cinchy platform. Please check back at a later time. For a full list of in-progress function translations, see the CQL functions reference page.
Syntax
SYSUTCDATETIME()
Return types
datetime2
Remarks
SQL statements can refer to SYSUTCDATETIME
anywhere they can refer to a datetime2 expression.
SYSUTCDATETIME
is a nondeterministic function. You can't index views and expressions that reference this function.
Example 1: Show formats returned by Date and Time functions
SELECT
SYSDATETIME() AS [SYSDATETIME()],
SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()],
SYSUTCDATETIME() AS [SYSUTCDATETIME()],
GETDATE() AS [GETDATE()],
GETUTCDATE() AS [GETUTCDATE()]
Example 2: Convert Date and Time to Date
SELECT
CONVERT(date, SYSDATETIME()),
CONVERT(date, SYSDATETIMEOFFSET()),
CONVERT(date, SYSUTCDATETIME()),
CONVERT(date, GETDATE()),
CONVERT(date, GETUTCDATE())
Example 3: Convert Date and Time to Time
DECLARE @DateTime DATETIME = GETDATE()
DECLARE @Time TIME
SELECT
@Time = CONVERT(time, @DateTime)
SELECT
@Time AS 'Time',
@DateTime AS 'Date Time'
Last updated