DATETIMEOFFSETFROMPARTS (Transact-SQL)

APPLIES TO: yesSQL Server (starting with 2012) yesAzure SQL Database yesAzure SQL Data Warehouse yesParallel Data Warehouse

Returns a datetimeoffset value for the specified date and time arguments. The returned value has a precision specified by the precision argument, and an offset as specified by the offset arguments.

Topic link icon Transact-SQL Syntax Conventions

Syntax

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )  

Arguments

year
An integer expression that specifies a year.

month
An integer expression that specifies a month.

day
An integer expression that specifies a day.

hour
An integer expression that specifies the hours.

minute
An integer expression that specifies the minutes.

seconds
An integer expression that specifies the seconds.

fractions
An integer expression that specifies a fractional seconds value.

hour_offset
An integer expression that specifies the hour portion of the time zone offset.

minute_offset
An integer expression that specifies the minute portion of the time zone offset.

precision
An integer literal value that specifies the precision of the datetimeoffset value that DATETIMEOFFSETFROMPARTS will return.

Return types

datetimeoffset( precision )

Remarks

DATETIMEOFFSETFROMPARTS returns a fully initialized datetimeoffset data type. The offset arguments represent the time zone offset. For omitted offset arguments, DATETIMEOFFSETFROMPARTS assumes a time zone offset of 00:00 - in other words, no time zone offset. For specified offset arguments, DATETIMEOFFSETFROMPARTS expects values for both arguments, and both values positive or negative. If minute_offset has a value and hour_offset has no value, DATETIMEOFFSETFROMPARTS will raise an error. DATETIMEOFFSETFROMPARTS will raise an error if the other arguments have invalid values. If at least one required arguments has a NULL value, then DATETIMEOFFSETFROMPARTS will return NULL. However, if the precision argument has a NULL value, then DATETIMEOFFSETFROMPARTS will raise an error.

The fractions argument depends on the precision argument. For example, for a precision value of 7, each fraction represents 100 nanoseconds; for a precision of 3, each fraction represents a millisecond. For a precision value of zero, the value of fractions must also be zero; otherwise, DATETIMEOFFSETFROMPARTS will raise an error.

This function supports remoting to SQL Server 2017 servers and above. It will not support remoting to servers that have a version below SQL Server 2017.

Examples

A. An example without fractions of a second

SELECT DATETIMEOFFSETFROMPARTS ( 2010, 12, 31, 14, 23, 23, 0, 12, 0, 7 ) AS Result;  

Here is the result set.

Result  
----------------------------------
2010-12-31 14:23:23.0000000 +12:00  
  
(1 row(s) affected)  

B. Example with fractions of a second

This example shows the use of the fractions and precision parameters:

  1. When fractions has a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.

  2. When fractions has a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.

  3. When fractions has a value of 500, and precision has a value of 3, then the value of fractions represents 500/1000 of a second.

SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 5, 12, 30, 1 );  
SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 50, 12, 30, 2 );  
SELECT DATETIMEOFFSETFROMPARTS ( 2011, 8, 15, 14, 30, 00, 500, 12, 30, 3 );  
GO  

Here is the result set.

----------------------------------  
2011-08-15 14:30:00.5 +12:30  
  
(1 row(s) affected)  
  
----------------------------------  
2011-08-15 14:30:00.50 +12:30  
  
(1 row(s) affected)  
  
----------------------------------  
2011-08-15 14:30:00.500 +12:30  
  
(1 row(s) affected)  

See also

datetimeoffset (Transact-SQL)
AT TIME ZONE (Transact-SQL)