In SQL Server, the DATETIME2FROMPARTS() function works similar to the DATETIMEFROMPARTS() function, except that it accepts 8 arguments and returns a datetime2 value. The DATETIMEFROMPARTS() function on the other hand, accepts just 7 arguments and it returns a datetime value.

Basically, you provide all the date/time parts and this function will return a datetime2 value based on the parts you provide.

Syntax

First, here’s the syntax:

DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Where each of the first 7 arguments is an integer expression that specifies that portion of the date. For example, year is an integer that specifies the year portion of the date.

The 8th argument is an integer expression that allows you to define the precision of the return value. This value specifies the scale (i.e. the number of digits to the right of the decimal place).

Example

Here’s an example of usage:

SELECT DATETIME2FROMPARTS( 2021, 05, 10, 23, 35, 29, 500, 4 ) AS Result;

Result:

+--------------------------+
| Result                   |
|--------------------------|
| 2021-05-10 23:35:29.0500 |
+--------------------------+

Invalid Values

All arguments need to be valid values. For example, you can’t specify a month of 13 or a minutes value of 61.

Here’s an example where I specify a month value of 13:

SELECT DATETIME2FROMPARTS( 2021, 13, 10, 23, 35, 29, 500, 4 ) AS Result;

Result:

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

Number of Arguments

You also need to provide the correct number of arguments (8):

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

Result:

The datetime2fromparts function requires 8 argument(s).

Null Values

If you provide a null value for any of the first 7 arguments, the result is NULL:

SELECT DATETIME2FROMPARTS( 2021, 05, 10, NULL, 35, 29, 500, 4 ) AS Result;

Result:

+----------+
| Result   |
|----------|
| NULL     |
+----------+

However, providing a null value for the 8th argument (which specifies the precision/scale) results in an error:

SELECT DATETIME2FROMPARTS( 2021, 05, 10, 23, 35, 29, 500, NULL ) AS Result;

Result:

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