SQL Server DATETIMEOFFSETFROMPARTS Function

Summary: in this tutorial, you will learn how to use the DATETIMEOFFSETFROMPARTS() function to construct a DATETIMEOFFSET from the date and times arguments.

SQL Server DATETIMEOFFSETFROMPARTS() function #

The DATETIMEOFFSETFROMPARTS() constructs a DATETIMEOFFSET value from the specified date and time arguments.

The following shows the syntax of DATETIMEOFFSETFROMPARTS() function:

DATETIMEOFFSETFROMPARTS ( year, month, day, hour, minute, seconds, fractions, hour_offset, minute_offset, precision )  
Code language: SQL (Structured Query Language) (sql)

The DATETIMEOFFSETFROMPARTS() function accepts the following arguments:

• year is an integer expression that resolves to a year.
• month is an integer expression that evaluates to a month that ranges from 1 to 12.
• day is an integer expression that identifies a day, ranging from 1 to 31
• hour is an integer expression that identifies the hours.
• minute is an integer expression that identifies the minutes.
• seconds is an integer expression that identifies the seconds.
• fractions is an integer expression that identifies a fractional seconds value.
• hour_offsetis an integer expression that specifies the hour portion of the time zone offset.
• minute_offsetis an integer expression that specifies the minute portion of the time zone offset.
• precision is an integer expression that identifies the precision of the DATETIMEOFFSET value.

SQL Server DATETIMEOFFSETFROMPARTS() function examples #

Let’s take some examples of using the DATETIMEOFFSETFROMPARTS() function.

Using DATETIMEOFFSETFROMPARTS() function to return a DATETIMEOFFSET example #

This example uses the DATETIMEOFFSETFROMPARTS() function to construct a DATETIMEOFFSET from date and time arguments:

SELECT 
    DATETIMEOFFSETFROMPARTS(2020, 11, 12, 20, 35, 30, 5000, 10, 30, 4) Result;
Code language: SQL (Structured Query Language) (sql)

Here is the output:

Result
----------------------------------
2020-11-12 20:35:30.5000 +10:30

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using DATETIMEOFFSETFROMPARTS() function with NULL arguments example #

The following example returns NULL because the year argument is NULL:

SELECT 
    DATETIMEOFFSETFROMPARTS(NULL, 11, 12, 20, 35, 30, 5000, 10, 30, 4) Result;
Code language: SQL (Structured Query Language) (sql)

This is the output:

Result
---------------------------
NULL

(1 row affected)
Code language: SQL (Structured Query Language) (sql)

Using DATETIMEOFFSETFROMPARTS() function with invalid arguments example #

This example uses invalid arguments that cause DATETIME2FROMPARTS() function returns an error:

SELECT 
    DATETIMEOFFSETFROMPARTS(2020, 19, 14, 20, 35, 30, 5000, 10, 30, 4) Result;
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

Cannot construct data type datetimeoffset, some of the arguments have values which are not valid.Code language: JavaScript (javascript)

In this tutorial, you have learned how to use the DATETIMEOFFSETFROMPARTS() function to construct a DATETIMEOFFSET value from the specified date and time arguments.