DATEFROMPARTS is one of the new built-in Date and Time Function introduced as a Part of Sql Server 2012. As name suggests it forms a DATE from it’s parts i.e. Year, Month and Day. It is a Sql Server native function not dependent on the .NET CLR.

SYNTAX: DATEFROMPARTS ( year, month, day )

WHERE: year, month and day are integer expressions representing valid year, month and day respectively.
Return Type: DATE

Example 1: DATEFROMPARTS – with valid date part integer constants

SELECT DATEFROMPARTS( 2013, 6, 23 ) AS 'DATE',
	DATEFROMPARTS( 1, 1, 1 ) AS 'MIN DATE',
	DATEFROMPARTS( 9999, 12, 31 ) AS 'MAX DATE'

Result:
DATE                    MIN DATE                 MAX DATE
———-                ———-                       ———-
2013-06-23       0001-01-01              9999-12-31

Example 2: DATEFROMPARTS – with valid date part integer variables

DECLARE @year INT = 2013, @month INT = 6, @day INT = 23
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
2013-06-23

Example 3: DATEFROMPARTS – with invalid month part

DECLARE @year INT = 2013, @invalidmonth INT = 15, @day INT = 23
SELECT DATEFROMPARTS( @year, @invalidmonth, @day) AS 'DATE'

Result:
Msg 289, Level 16, State 1, Line 2
Cannot construct data type date, some of the arguments have values which are not valid.

Example 4: DATEFROMPARTS – with one of the part as NULL

DECLARE @year INT = 2013, @month INT = 6, @day INT = NULL
SELECT DATEFROMPARTS( @year, @month, @day) AS 'DATE'

Result:
DATE
———-
NULL

You may like to read the below new built-in function’s introduced in Sql Server 2012: