I am determined to write 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 DATEFROMPARTS() function. This function takes the year, month and day as inputs and returns a date variable as an output.
The examples below depict sample calls to the function. Valid integers entered in either numeric or text format result in a correct date.
Any NULL values in the input parameters equate to a overall NULL value. Please note that a empty strings or invalid integers return errors with severity 16.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
-- -- Additional functions to convert int to date -- -- Convert three numbers (y, m, d) to a datetime SELECT DATEFROMPARTS(2014, 12, 31) AS MY_DATE1_VALID1, DATEFROMPARTS('2014', '12', '31') AS MY_DATE1_VALID2 GO -- A null value returns null SELECT DATEFROMPARTS(NULL, NULL, NULL) AS MY_DATE1_NULL GO -- Empty strings error out SELECT DATEFROMPARTS('', '', '') AS MY_DATE1_EMPTY GO -- Invalid strings error out SELECT DATEFROMPARTS(-2014, -12, -31) AS MY_DATE1_INVALID GO |
The output from the above test is listed below.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
output: MY_DATE1_VALID1 MY_DATE1_VALID2 --------------- --------------- 2014-12-31 2014-12-31 MY_DATE1_NULL ------------- NULL MY_DATE1_EMPTY -------------- Msg 289, Level 16, State 1, Line 2 Cannot construct data type date, some of the arguments have values which are not valid. MY_DATE1_INVALID ---------------- Msg 289, Level 16, State 1, Line 2 Cannot construct data type date, some of the arguments have values which are not valid. |
The short and sweet summary is that the DATEFROMPARTS function takes valid integers as input for year, month and date. It returns a date variable representing the parts. Any NULL input values result in NULL output values. Last but not least, any invalid input results in a ERROR.
