SQL Server DATETIME2FROMPARTS Function

Summary: in this tutorial, you will learn how to use the SQL Server DATETIME2FROMPARTS() function to construct a DATETIME2 value from a year, month, day, hour, minute, seconds, fractions, and precision value.

SQL Server DATETIME2FROMPARTS() function overview

The DATETIME2FROMPARTS() function returns a date value constructed from the year, month, day, hour, minute, seconds, fractions, and precision value.

The following illustrates the syntax of the DATETIME2FROMPARTS() function:

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

The DATETIME2FROMPARTS() function accepts eight 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.
precision is an integer expression that identifies the precision of the DATETIME2 value.

The DATETIME2FROMPARTS() function returns a value of the DATETIME2 type. If any argument is NULL, the function will return NULL.

If one argument has an invalid value, the DATETIME2FROMPARTS() function will raise an error.

SQL Server DATETIME2FROMPARTS() function examples

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

Using DATETIME2FROMPARTS() function to return a DATETIME2 example

The following example uses the DATETIME2FROMPARTS() function to construct a DATETIME2 from the year, month, day, hour, minute, seconds, fractions, and precision values:

SELECT 
    DATETIME2FROMPARTS ( 2020, 12, 31, 11, 59, 59, 0, 0 ) result;  
Code language: SQL (Structured Query Language) (sql)

Here is the output:

result
---------------------------
2020-12-31 11:59:59

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

Using DATETIME2FROMPARTS() function with NULL arguments example

This example returns NULL because the month argument is NULL:

SELECT 
    DATETIME2FROMPARTS(2020, NULL, 31, 11, 59, 59, 0, 0) result;
Code language: SQL (Structured Query Language) (sql)

The output is as follows:

result
---------------------------
NULL

(1 row affected)Code language: PHP (php)

Using DATETIME2FROMPARTS() function with invalid values in arguments example

The following example passes invalid arguments to the DATETIME2FROMPARTS() function that results in an error:

SELECT 
    DATETIME2FROMPARTS(2020, 13, 31, 11, 59, 59, 0, 0) result;
Code language: SQL (Structured Query Language) (sql)

Here is the error message:

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

In this tutorial, you have learned how to use the DATETIME2FROMPARTS() function to construct a DATETIME2 value from its components including year, month, day, hour, minute, seconds, fractions, and precision value.

Was this tutorial helpful?