SQL Server DATEFROMPARTS Function
The DATEFROMPARTS function returns a date when separate values for year, month and day are provided.
DATEFROMPARTS(year, month, day)
- 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
- 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