SQL DATETIMEOFFESTFROMPARTS

SQL DATETIMEOFFESTFROMPARTS function is one of the Date and Time Function. It is used to return a date-time offset value from the user’s specified date and time parts along with fractions, and precision.

The basic syntax of the DATETIMEOFFESTFROMPARTS Function in SQL Server is as follows:

DATETIMEOFFESTFROMPARTS (year, month, day, hour, minute, seconds, fractions, 
                         hours_offset, minutes_offset, precision)

This function returns datetimeoffset data type value as output. Here, the Fraction value always depends upon the Precision. If the precision value is 4, the fraction value must also be 4 (or less than 4); otherwise, it will throw an error.

SQL DATETIMEOFFESTFROMPARTS Function Example

In this example, we will show you the use of SQL Server DateTimeOffsetFromParts function

-- Example for SQL DATETIMEOFFSETFROMPARTS Function
SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, 25, 09, 3, 12, 30, 1 ) AS Result

SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, 25, 09, 36, 12, 30, 2 ) AS Result

SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, 25, 09, 363, 12, 30, 3 ) AS Result

-- Let me try the NULL values
SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, NULL, 09, 363, 12, 30, 3 ) AS Result

-- Precision Value is greater than Fraction
SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, 25, 09, 363, 12, 30, 7 ) AS Result

-- Precision Value is Les than Fraction. Invalid argument
SELECT DATETIMEOFFSETFROMPARTS ( 2017, 11, 19, 16, 25, 09, 363, 12, 30, 2 ) AS Result

As you can see from the below screenshot, the last statement is throwing an error. This is because, Fraction value = 363, and Precision = 2 (does not match).

Let me show you the result