SQL DATEFROMPARTS Function Use and Examples

Overview

The DATEFROMPARTS function returns a date when separate values for year, month and day are provided.

Explanation

Syntax

DATEFROMPARTS(year, month, day)

Parameters

• Year – Is an integer value of the year provided starting at 1.
• Month – Is an integer value of the month provided between 1 and 12.
• Day – Is an integer value of the year provided starting at 1.

Simple DATEFROMPARTS Example

The following example will show a simple DATEFROMPARTS example.

SELECT DATEFROMPARTS(2013, 3 ,2) as date

Note that you can use 3 or 03 and get the same result

SELECT DATEFROMPARTS(2013,0 3, 02) as date

Using a 2 digit year with DATEFROMPARTS

If 2 digits are used for the year it will show the year with preceding zeros.

SELECT DATEFROMPARTS(13, 03, 02) as date

Cannot Construct Data Type Date Error with DATEFROMPARTS

If the arguments are not valid, you will receive an invalid arguments error. The following example shows the error message.

SELECT DATEFROMPARTS(-13, 03, 02) as date

The error message is the following. This problem occurs because you cannot use negative numbers.

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

A similar error message occurs when a 0 value is used for any of the parameters.

SELECT DATEFROMPARTS(0, 03, 00) as date

Also, you get the same error if one of the values is out of range such as 13 for the month. 

SELECT DATEFROMPARTS(2013,13,02) as date

NULL values with DATEFROMPARTS

If one of the arguments is null, the function will return null.

SELECT DATEFROMPARTS(2013, NULL, 02) as date

Example with Table Data

The following example will show how to work with tables we are showing employees with the hire date equal to May 3, 2009.

DECLARE @year int, @month int, @day int
SELECT @year = 2009, @month = 5, @day = 3
 
SELECT BusinessEntityID, HireDate
FROM HumanResources.Employee
WHERE HireDate=DATEFROMPARTS(@year, @month, @day)

Here is another example:

CREATE TABLE #temp (year int, month int, day int)
 
INSERT INTO #temp VALUES(2021,1,1), (2021,1,15), (2020,5,5)
 
SELECT DATEFROMPARTS([year], [month], [day])
FROM #temp

Additional Information

• How to Get Current Date in SQL Server
• Mimic timestamp behavior of other database platforms to store last modified date
• SQL Convert Date to YYYYMMDD

Daniel Calbimonte

Daniel Calbimonte is a Microsoft Most Valuable Professional, Microsoft Certified Trainer and Microsoft Certified IT Professional for SQL Server. He is an accomplished SSIS author, teacher at IT Academies and has over 10 years of experience as a QE and developer for SQL Server related software. He has worked for the government, oil companies, web sites, magazines and universities around the world. Daniel also regularly speaks at SQL Servers conferences and blogs.

• MSSQLTips Awards: Author of the Year Contender – 2015-2018, 2022, 2023 | Champion (100+ tips) – 2018