DATETIMEOFFSETFROMPARTS (Transact-SQL)
Applies to:
SQL Server
Azure SQL Database
Azure SQL Managed Instance
Azure Synapse Analytics
Analytics Platform System (PDW)
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.
Transact-SQL syntax conventions
Syntax
DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )
Note
To view Transact-SQL syntax for SQL Server 2014 (12.x) and earlier versions, see Previous versions documentation.
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 is capable of being remoted to SQL Server 2012 (11.x) servers and above. It is not remoted to servers that have a version below SQL Server 2012 (11.x).
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:
When fractions has a value of 5, and precision has a value of 1, the value of fractions represents 5/10 of a second.
When fractions has a value of 50, and precision has a value of 2, the value of fractions represents 50/100 of a second.
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)