DATETIMEOFFSETFROMPARTS (Transact-SQL)

Artikel
09/03/2024

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 )

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's 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's 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)

Dela via