To create a string as input for a datetime value, you need to reformat the date and time formatted integers. In this example, you need to dissect the integer value and turn it into mm-dd-yyyy format to follow the client's mdy setting. You can use other separators, but for simplicity, let's use the hyphen (-). To create this format you need to piece together the components. To extract the month value and place it in the first position, you need the two characters starting at the fifth character in the integer. Next, you need to add the hyphen separator. To extract the day value and place it in the second position, you need the two characters starting at the seventh character in the integer. Between the second and third positions, you need another hyphen separator. Finally, to extract the year value, you need the four characters starting at the first character in the integer. The end result for the 20020901 integer value should be the string '09-01-2002.' You can separate this value from the time value by placing a space between the two values. Then, the time will follow the space, and colons will separate the time's hours, minutes, and seconds values. The completed date and time string for row 1's starting date and time will be '09-01-2002 10:27:45' and will represent AM, because all times without AM or PM within the string use the 24-hour clock.
The code in Listing 3, page 12, shows the completed function that returns a real datetime value from the two formatted integers in the ItemsRun table. The following examples show how you can use the StartDate and StartTime columns to produce one column of both date and time:
SELECT ItemID,
dbo.DisplayDateFromINT(StartDate,
StartTime, 109) AS 'Start Date & Time',
dbo.DisplayDateFromINT(EndDate,
EndTime, 109) AS 'End Date & Time'
FROM dbo.ItemsRun
Additionally, if you want to see the difference between the two real datetime values in seconds, you can use DATEDIFF()and DisplayDateFromINT together. Calling functions within the input values to another function is completely legitimate. For example, you can make two function calls to DisplayDateFromINT within DATEDIFF(), as the following SELECT statement shows:
SELECT ItemID,
DATEDIFF(ss,
dbo.DisplayDateFromINT(StartDate,
StartTime, 109),
dbo.DisplayDateFromINT(EndDate, EndTime, 109))
AS 'Total Time in Seconds'
FROM dbo.ItemsRun
Notice that in these calls, you didn't need to convert the date values. Style 109 is an appropriate format for inputting a string value to the DATEDIFF() function because it returns a date in mdy format. However, user or application changes to the session settings could cause an out-of-range problem or invalid data values.
If you're concerned about the effect of the DATEFORMAT() session setting because applications or users in your environment change them, review and test the downloadable UserDefinedFunctionExamples.sql sample code that changes the DATEFORMAT() setting from mdy to dmy. This change is one of the most common changes to the DATEFORMAT() setting and produces an interesting effect with some dates. For example, the date January 10 might show up as October 1 because the values for day and month (1 and 10) are valid values for both month and day. Dates such as January 31 cause an overflow because 31 isn't valid for month. Regardless of date, the information is wrong unless you're consistent about the session setting that defines the order in which you list day and month. To head off user changes, you might need to force these session settings by using a stored procedure instead. All you need to do is set the session setting at the beginning of the stored procedure, then call the function from within the stored procedure, as Listing 4 shows.
Note that although explicitly setting the DATEFORMAT() value within a stored procedure usually isn't a problem, I don't recommend changing session settings within stored procedures. Changing some (not all) session settings can cause side effects that compromise performance. For more information about the effects of certain session settings, see the SQL Server Books Online (BOL) topic "SET Options that Affect Results" and the Microsoft article "Troubleshooting Stored Procedure Recompilation" at http://support.microsoft.com/default.aspx?scid=KB;EN-US;Q243586&.
Finally, as I noted earlier, another use of a new datetime value is to order the data based on when the item started running or when the item finished. Because the function returns the value as a string, you need to convert it back to a date so that it ends up in the order you want. If you don't change it back to a date, the string value (April, August, December, February, January, July, June, March, May, November, October, September)—instead of the date value—will order your data alphabetically. To keep the string value from dictating the ordering of the data, you can use the function within the CONVERT() function (which resides in the ORDER BY clause), as the following statement shows:
SELECT ItemID,
dbo.DisplayDateFromINT(StartDate, StartTime, 109) AS 'Start Date',
dbo.DisplayDateFromINT(EndDate, EndTime, 109) AS 'End Date'
FROM dbo.ItemsRun
ORDER BY CONVERT(datetime,
dbo.DisplayDateFromINT(StartDate, StartTime, 109))
Scalar UDFs are reusable and flexible because they can be parameterized. You can use functions within functions, stored procedures, views, and constraints to simplify as well as extend their usage. To encapsulate simple expressions, you can always consider hard-coding the expression in a view, but if you want to reuse the code, a function is a better choice.