SQL DATETIMEOFFSETFROMPARTS 2

SQL DATETIME OFFESET FROM PARTS

by

SQL DATETIME OFFESET FROM PARTS 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 syntax of the DATETIME OFFESET FROM PARTS Function in SQL Server is as follows:

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

This SQL Server 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 DATETIME OFFESET FROM PARTS Function Example

In this example, we will show you the use of SQL Server DateTime Offset From Parts 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

The last statement of Date and Time Function is throwing an error. This is because, Fraction value = 363, and Precision = 2 (does not match).

SQL DATETIMEOFFSETFROMPARTS 1

Let me show you the result

SQL DATETIMEOFFSETFROMPARTS 2