DATETIME2FROMPARTS() – Date/Time Functions

I am bent on writing another short articles or tidbits on Transaction SQL date/time functions.

In software engineering, the presentation layer, the business logic layer, and the data access layer might be written using different technologies and on different servers.

For instance, Web Forms (ASP.NET) might be used in the presentation layer, Web Services (C#.NET) might implement the business application layer, and SQL Server (TSQL) might constitute the data access layer.

With this design, the application (presentation tier) might have the end user choose month, day and year from a drop down list boxes. How do we convert this input into a date time variable in SQL Server (data tier)?

There are six new functions introduced in SQL Server 2012 that can solve this problem.

Today, I will be exploring the DATETIME2FROMPARTS() function. This function takes the year, month, day, hour, minutes, seconds, fractions of a second, and precision as inputs and returns a datetime2 variable as output.

The examples below depict sample call to the function. Valid integers entered in either numeric or text format result in a correct date.

The output from the above test is listed below.

I will leave negative test cases for you to explore. However, any NULL input values result in NULL output values. Any invalid input results in a ERROR.

The short and sweet summary is that the DATETIME2FROMPARTS function takes valid integers as input for year, month, day, hour, minutes, seconds, fractions of a second, and precision; and returns a datetime2 variable representing the parts.

Related posts

Leave a Comment