SQL Server DATEFROMPARTS Function


By:

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
DATEFROMPARTS SIMPLE EXAMPLE

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

SELECT DATEFROMPARTS(2013,0 3, 02) as date
DATEFROMPARTS WITH ZERO PREFIX

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
DATEPART 2 DIGIT YEAR

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
NULL values in DATEFROMPARTS

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)
DATEFROMPARTS with tables

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
DATEFROMPARTS with tables

Related Articles


Last Update: 1/14/2022




Comments For This Article





download














get free sql tips
agree to terms