SQL Server DATEFROMPARTS Function

By: Daniel Calbimonte

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

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.

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

Related Articles

• 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