You can use the T-SQL DATEFROMPARTS() function in SQL Server to return a date value that maps to the date parts that you provide as integer expressions.

The way it works is, you provide three values (one for each of the year, month, and day values), and SQL Server will return a date value based on the values you provide.

Examples below.

Syntax

First, here’s the syntax:

DATEFROMPARTS ( year, month, day )

Here’s what each argument can be:

year

An integer expression that specifies a year.

month

An integer expression that specifies a month, from 1 to 12.

day

An integer expression that specifies a day.

Example

Here’s an example of usage:

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

Result:

+------------+
| Result     |
|------------|
| 2021-05-10 |
+------------+

Invalid Values

You’ll need to ensure that the date parts are provided in the correct order, otherwise you could end up with an error. Worse yet, you might not get an error (if the day portion is 12 or less), and therefore could miss a major problem with the way the date is translated.

Here’s an example of an invalid value:

SELECT DATEFROMPARTS( 2021, 30, 10 ) AS Result;

Result:

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

This occurs because we provided 30 as the month part. This part will only accept values between 1 and 12.

Null Values

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

Here’s an example using a null value:

SELECT DATEFROMPARTS( 2021, NULL, 10 ) AS Result;

Result:

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

Therefore, it probably goes without saying (but I’ll say it anyway), that if all arguments are null values, the result is NULL:

SELECT DATEFROMPARTS( NULL, NULL, NULL ) AS Result;

Result:

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