Skip to content

sysdatetimeoffset-transact-sql.md

Latest commit

 

History

History
145 lines (119 loc) · 5.61 KB

sysdatetimeoffset-transact-sql.md

File metadata and controls

145 lines (119 loc) · 5.61 KB

title: "SYSDATETIMEOFFSET (Transact-SQL) | Microsoft Docs" ms.custom: "" ms.date: "03/14/2017" ms.prod: "sql" 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" monikerRange: ">= aps-pdw-2016 || = azuresqldb-current || = azure-sqldw-latest || >= sql-server-2016 || = sqlallproducts-allversions"

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)