SMALLDATETIMEFROMPARTS() – Date/Time Function

I am steadfast in 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 SMALLDATETIMEFROMPARTS() function. This function takes the year, month, day, hour and minutes as inputs and returns a small date time 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 SMALLDATETIMEFROMPARTS function takes valid integers as input for year, month, day, hour and minutes; and returns a small date time variable representing the parts.

Related posts

Leave a Comment