DateTime2FromParts function in SQL Server 2012

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]

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]

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

Link for all dot net and sql server video tutorial playlists
https://www.youtube.com/user/kudvenkat/playlists?sort=dd&view=1

Link for slides, code samples and text version of the video
http://csharp-video-tutorials.blogspot.com/2015/10/datetime2fromparts-function-in-sql.html