Skip to content

sysdatetimeoffset-transact-sql.md

Latest commit

 

History

History
144 lines (118 loc) · 5.49 KB

sysdatetimeoffset-transact-sql.md

File metadata and controls

144 lines (118 loc) · 5.49 KB
title SYSDATETIMEOFFSET (Transact-SQL) | Microsoft Docs
ms.custom
ms.date 03/14/2017
ms.prod sql-non-specified
ms.prod_service database-engine, sql-database, sql-data-warehouse, pdw
ms.service
ms.component t-sql|functions
ms.reviewer
ms.suite sql
ms.technology
database-engine
ms.tgt_pltfrm
ms.topic language-reference
f1_keywords
SYSDATETIMEOFFSET_TSQL
SYSDATETIMEOFFSET
dev_langs
TSQL
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
ms.assetid 8423c753-cebe-4edd-871d-0138e092199f
caps.latest.revision 43
author edmacauley
ms.author edmaca
manager craigg
ms.workload Active

SYSDATETIMEOFFSET (Transact-SQL)

[!INCLUDEtsql-appliesto-ss2008-all-md]

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).

Topic link icon 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)