Skip to content

Latest commit

 

History

History
137 lines (109 loc) · 5.38 KB

sysdatetime-transact-sql.md

File metadata and controls

137 lines (109 loc) · 5.38 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic ms.custom f1_keywords helpviewer_keywords dev_langs monikerRange
SYSDATETIME (Transact-SQL)
SYSDATETIME (Transact-SQL)
MikeRayMSFT
mikeray
03/14/2017
sql
t-sql
reference
ignite-2024
SYSDATETIME_TSQL
SYSDATETIME
dates [SQL Server], functions
date and time [SQL Server], SYSDATETIME
current date and time [SQL Server]
functions [SQL Server], time
system date and time [SQL Server]
system time [SQL Server]
SYSDATETIME function [SQL Server]
functions [SQL Server], date and time
time [SQL Server], functions
dates [SQL Server], current date and time
dates [SQL Server], system date and time
time [SQL Server], system
TSQL
>=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric

SYSDATETIME (Transact-SQL)

[!INCLUDE sql-asdb-asdbmi-asa-pdw-fabricse-fabricdw]

Returns a datetime2(7) value that contains the date and time of the computer on which the instance of [!INCLUDEssNoVersion] 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.

Azure SQL Database (with the exception of Azure SQL Managed Instance) and Azure Synapse Analytics follow UTC. Use AT TIME ZONE in Azure SQL Database or Azure Synapse Analytics if you need to interpret date and time information in a non-UTC time zone.

For an overview of all [!INCLUDEtsql] date and time data types and functions, see Date and Time Data Types and Functions (Transact-SQL).

:::image type="icon" source="../../includes/media/topic-link-icon.svg" border="false"::: Transact-SQL syntax conventions

Syntax

SYSDATETIME ( )

Return Type

datetime2(7)

Remarks

[!INCLUDEtsql] 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

[!INCLUDEssNoVersion] 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 [!INCLUDEssNoVersion] is running. The precision of this API is fixed at 100 nanoseconds. The accuracy can be determined by using the GetSystemTimeAdjustment() Windows API.

Examples

The following examples use the six [!INCLUDEssNoVersion] 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  
*/

Examples: [!INCLUDEssazuresynapse-md] and [!INCLUDEssPDW]

D: Getting the current system date and time

SELECT SYSDATETIME();

[!INCLUDEssResult]

--------------------------  
7/20/2013 2:49:59 PM

See Also

CAST and CONVERT (Transact-SQL)
Date and Time Data Types and Functions (Transact-SQL)