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.