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.