Skip to content

sysdatetimeoffset-transact-sql.md

Latest commit

 

History

History
137 lines (110 loc) · 5.3 KB

sysdatetimeoffset-transact-sql.md

File metadata and controls

137 lines (110 loc) · 5.3 KB
title SYSDATETIMEOFFSET (Transact-SQL)
description SYSDATETIMEOFFSET (Transact-SQL)
author MikeRayMSFT
ms.author mikeray
ms.date 03/14/2017
ms.service sql
ms.subservice t-sql
ms.topic reference
ms.custom
ignite-2025
f1_keywords
SYSDATETIMEOFFSET_TSQL
SYSDATETIMEOFFSET
helpviewer_keywords
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
dev_langs
TSQL
monikerRange >=aps-pdw-2016 || =azuresqldb-current || =azure-sqldw-latest || >=sql-server-2016 || >=sql-server-linux-2017 || =azuresqldb-mi-current || =fabric-sqldb

SYSDATETIMEOFFSET (Transact-SQL)

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

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)