SQL Server DATEFROMPARTS() Function

Summary: in this tutorial, you will learn how to use the SQL Server DATEFROMPARTS() function to construct a date from year, month, and day.

SQL Server DATEFROMPARTS() function overview

The DATEFROMPARTS() function returns a DATE value that maps to a year, month, and day.

Here’s the syntax of the DATEFROMPARTS() function:

DATEFROMPARTS(year, month, day)Code language: SQL (Structured Query Language) (sql)

The DATEFROMPARTS() function accepts three arguments:

• year is an integer expression that resolves to a year
• month is an integer expression that evaluates to a month, ranging from 1 to 12.
• day is an integer expression that specifies a day, ranging from 1 to 31

The DATEFROMPARTS() function returns a DATE value. If any argument is NULL, the function will return NULL.

SQL Server DATEFROMPARTS() function examples

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

1) Basic SQL Server DATEFROMPARTS() function example

The following example uses the DATEFROMPARTS() function to construct a date from year, month, and day values:

SELECT 
    DATEFROMPARTS(2020,12,31) a_date;Code language: SQL (Structured Query Language) (sql)

Here is the output:

a_date
----------
2020-12-31

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

2) Using the DATEFROMPARTS() function with NULL example

The following example returns NULL because the month argument is NULL:

SELECT 
    DATEFROMPARTS(2020,null,31) a_date;Code language: SQL (Structured Query Language) (sql)

Output:

a_date
----------
NULL

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

3) Using the DATEFROMPARTS() function with invalid arguments

The following statement uses the DATEFROMPARTS() function with an invalid day value (-1), which results in an error:

SELECT 
    DATEFROMPARTS(2020,20,-1) a_date;Code language: SQL (Structured Query Language) (sql)

The following shows the error message:

Cannot construct data type date, some of the arguments have values which are not valid.Code language: SQL (Structured Query Language) (sql)

Summary

• Use the DATEFROMPARTS()function to construct a DATE value from its part including year, month, and day.