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:
New Built in Functions introduced in Sql Server | |
CONVERSION FUNCTIONS | |
PARSE | TRY_PARSE |
TRY_CONVERT | |
STRING FUNCTIONS | |
CONCAT | FORMAT |
LOGICAL FUNCTIONS | |
CHOOSE | IIF |
DATE AND TIME FUNCTIONS | |
EOMONTH | |
DATEFROMPARTS | DATETIMEFROMPARTS |
SMALLDATETIMEFROMPARTS | DATETIME2FROMPARTS |
TIMEFROMPARTS | DATETIMEOFFSETFROMPARTS |
6 thoughts on “DATEFROMPARTS FUNCTION IN SQL SERVER 2012”