Support us .Net Basics C# SQL ASP.NET ADO.NET MVC Slides C# Programs Subscribe Buy DVD

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 : 
datetime2fromparts function in sql server 2012

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 : 
datetimefromparts sql 2012

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

2 comments:

  1. Sir,
    Please discuss the difference between
    DATE and DATETIME

    Thanks

    ReplyDelete
  2. how to print 'year-month-day-hr-min-sec-millisecond'

    ReplyDelete

If you like this website, please share with your friends on facebook and Google+ and recommend us on google using the g+1 button on the top right hand corner.