DATEFROMPARTS function in SQL Server

we will discuss DATEFROMPARTS function in SQL Server

DATEFROMPARTS function

• Introduced in SQL Server 2012
• Returns a date value for the specified year, month, and day
• The data type of all the 3 parameters (year, month, and day) is integer
• If invalid argument values are specified, the function returns an error
• If any of the arguments are NULL, the function returns null

Syntax : DATEFROMPARTS ( year, month, day )

Example : All the function arguments have valid values, so DATEFROMPARTS returns the expected date

SELECT DATEFROMPARTS ( 2015, 10, 25) AS [Date]

Output :


Example : Invalid value specified for month parameter, so the function returns an error

SELECT DATEFROMPARTS ( 2015, 15, 25) AS [Date]

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

Example : NULL specified for month parameter, so the function returns NULL.

SELECT DATEFROMPARTS ( 2015, NULL, 25) AS [Date]

Output : 


Other new date and time functions introduced in SQL Server 2012

• EOMONTH (Discussed in Part 125 of SQL Server tutorial)

• DATETIMEFROMPARTS : Returns DateTime
• Syntax : DATETIMEFROMPARTS ( year, month, day, hour, minute, seconds, milliseconds )

• SMALLDATETIMEFROMPARTS : Returns SmallDateTime
• Syntax : SMALLDATETIMEFROMPARTS ( year, month, day, hour, minute )

• We will discuss the following functions in a later video
• TIMEFROMPARTS
• DATETIME2FROMPARTS
• DATETIMEOFFSETFROMPARTS

In our next video we will discuss the difference between DateTime and SmallDateTime.