T-SQL has a function called DATETIMEOFFSETFROMPARTS() that allows you to get a datetimeoffset value from the various separate parts of a date. Specifically, it returns a datetimeoffset value for the specified date and time and with the specified offsets and precision.

Examples of this function below.

Syntax

The syntax goes like this:

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

Where:

• The first 7 arguments are integer expressions specifying that particular date/time part.
• The hour_offset argument is an integer expression specifying the hour portion of the time zone offset.
• The minute_offset is an integer expression specifying the minute portion of the time zone offset.
• The precision argument is an integer literal specifying the precision of the datetimeoffset value to be returned. This value actually specifies the scale (i.e. the number of digits to the right of the decimal place).

Example

Here’s an example of usage.

SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 23, 35, 29, 500, 12, 30, 4 ) 
AS Result;

Result:

Result
----------------------------------
2021-05-10 23:35:29.0500 +12:30

Invalid Arguments

You need to ensure that all arguments are valid, otherwise you’ll get an error. Here’s an example of providing an out of range hour part (25). The hour part can only be between 0 and 24.

SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 25, 35, 29, 500, 12, 30, 4 ) 
AS Result;

Result:

Cannot construct data type datetimeoffset, some of the arguments have values which are not valid.

Number of Arguments

You need to provide the correct number of arguments (10). If you don’t you’ll get an error.

SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10 ) AS Result;

Result:

The datetimeoffsetfromparts function requires 10 argument(s).

Null Arguments

If any of the first 9 arguments are null, the result is NULL:

SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, NULL, 35, 29, 500, 12, 30, 4 ) 
AS Result;

Result:

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

However, if the last (precision) argument is null, then an error is returned:

SELECT DATETIMEOFFSETFROMPARTS( 2021, 05, 10, 23, 35, 29, 500, 12, 30, NULL ) 
AS Result;

Result:

Scale argument is not valid. Valid expressions for data type datetimeoffset scale argument are integer constants and integer constant expressions.

Also see DATETIME2FROMPARTS() Examples in SQL Server (T-SQL) for returning a datetime2 value (without the offset).