In SQL Server, the DATETIMEFROMPARTS() function works just like the DATEFROMPARTS() function, except that it also returns the time portion of the date.

The DATETIMEFROMPARTS() function accepts seven arguments, each representing a different part of the date/time. The function then returns a datetime value from the given parts.

Syntax

Here’s how the syntax goes:

DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

Where each argument 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.

Example

Here’s an example that demonstrates how it works:

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

Result:

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

So you can see that each of our arguments appears in the applicable part of the result.

Invalid Values

The values need to be within a valid range for that particular date part. For example, you can’t specify an hour part to be 30 (it needs to be between 0 and 24). Example:

SELECT DATETIMEFROMPARTS( 2021, 05, 10, 30, 35, 29, 500 ) AS Result;

Result:

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

Number of Arguments

You also need to provide the correct number of arguments (7). If you don’t, you’ll get an error message explaining this:

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

Result:

The datetimefromparts function requires 7 argument(s). 

Null Values

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

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

Result:

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