DateTime2FromParts function in SQL Server 2012

Suggested Videos
Part 125 - EOMONTH function in SQL Server 2012
Part 126 - DATEFROMPARTS function in SQL Server
Part 127 - Difference between DateTime and SmallDateTime in SQL Server



In this video we will discuss DateTime2FromParts function in SQL Server 2012.



DateTime2FromParts function

• Introduced in SQL Server 2012
• Returns DateTime2
• The data type of all the parameters is integer
• If invalid argument values are specified, the function returns an error
• If any of the required arguments are NULL, the function returns null
• If the precision argument is null, the function returns an error

Syntax : DATETIME2FROMPARTS ( year, month, day, hour, minute, seconds, fractions, precision )

Example : All the function arguments have valid values, so DATETIME2FROMPARTS returns DATETIME2 value as expected.

SELECT DATETIME2FROMPARTS ( 2015, 11, 15, 20, 55, 55, 0, 0 ) AS [DateTime2]

Output : 


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

SELECT DATETIME2FROMPARTS ( 2015, 15, 15, 20, 55, 55, 0, 0 ) AS [DateTime2]

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

Example : If any of the required arguments are NULL, the function returns null. NULL specified for month parameter, so the function returns NULL.

SELECT DATETIME2FROMPARTS ( 2015, NULL, 15, 20, 55, 55, 0, 0 ) AS [DateTime2]

Output : 


Example : If the precision argument is null, the function returns an error

SELECT DATETIME2FROMPARTS ( 2015, 15, 15, 20, 55, 55, 0, NULL ) AS [DateTime2]

Output : Scale argument is not valid. Valid expressions for data type datetime2 scale argument are integer constants and integer constant expressions.

TIMEFROMPARTS : Returns time value

Syntax : TIMEFROMPARTS ( hour, minute, seconds, fractions, precision )

Next video : We will discuss the difference between DateTime and DateTime2 in SQL Server