Skip to content

Latest commit

 

History

History
134 lines (108 loc) · 5.24 KB

sysdatetimeoffset-transact-sql.md

File metadata and controls

134 lines (108 loc) · 5.24 KB
title description author ms.author ms.date ms.service ms.subservice ms.topic f1_keywords helpviewer_keywords dev_langs monikerRange
SYSDATETIMEOFFSET (Transact-SQL)
SYSDATETIMEOFFSET (Transact-SQL)
MikeRayMSFT
mikeray
03/14/2017
sql
t-sql
reference
SYSDATETIMEOFFSET_TSQL
SYSDATETIMEOFFSET
date and time [SQL Server], SYSDATETIMEOFFSET
dates [SQL Server], functions
current date and time [SQL Server]
functions [SQL Server], time
system date and time [SQL Server]
system time [SQL Server]
SYSDATETIMEOFFSET 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 zones [SQL Server]
time [SQL Server], system
TSQL
>= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || >= sql-server-linux-2017 || = azuresqldb-mi-current

SYSDATETIMEOFFSET (Transact-SQL)

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

Returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of [!INCLUDEssNoVersion] is running. The time zone offset is included.

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

SYSDATETIMEOFFSET ( )

Return Type

datetimeoffset(7)

Remarks

[!INCLUDEtsql] statements can refer to SYSDATETIMEOFFSET anywhere they can refer to a datetimeoffset expression.

SYSDATETIMEOFFSET 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. Showing the formats that are returned by the date and time functions

The following example shows the different formats that are returned by the date and time functions.

SELECT SYSDATETIME() AS [SYSDATETIME()]  
    ,SYSDATETIMEOFFSET() AS [SYSDATETIMEOFFSET()]  
    ,SYSUTCDATETIME() AS [SYSUTCDATETIME()]  
    ,CURRENT_TIMESTAMP AS [CURRENT_TIMESTAMP]  
    ,GETDATE() AS [GETDATE()]  
    ,GETUTCDATE() AS [GETUTCDATE()];

[!INCLUDEssResult]

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. Converting date and time to date

The following example shows you how to convert date and time values to date.

SELECT CONVERT (date, SYSDATETIME())  
    ,CONVERT (date, SYSDATETIMEOFFSET())  
    ,CONVERT (date, SYSUTCDATETIME())  
    ,CONVERT (date, CURRENT_TIMESTAMP)  
    ,CONVERT (date, GETDATE())  
    ,CONVERT (date, GETUTCDATE());

[!INCLUDEssResult]

2007-04-30
2007-04-30
2007-04-30
2007-04-30
2007-04-30
2007-04-30

C. Converting date and time to times

The following example shows you how to convert date and time values to time.

SELECT CONVERT (time, SYSDATETIME()) AS [SYSDATETIME()]  
    ,CONVERT (time, SYSDATETIMEOFFSET()) AS [SYSDATETIMEOFFSET()]  
    ,CONVERT (time, SYSUTCDATETIME()) AS [SYSUTCDATETIME()]  
    ,CONVERT (time, CURRENT_TIMESTAMP) AS [CURRENT_TIMESTAMP]  
    ,CONVERT (time, GETDATE()) AS [GETDATE()]  
    ,CONVERT (time, GETUTCDATE()) AS [GETUTCDATE()];

[!INCLUDEssResult]

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

See Also

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