Listing 2 creates and populates the ItemsRun test table, which is composed of four rows. For each item (i.e., each row), the code defines the ItemID, StartDate, StartTime, EndDate, and EndTime columns. Two groups use these values. Applications use them internally to control and track the time that items started and ended. Developers designed these values so that they could effectively write their own date-and-time manipulation algorithms for display in their custom applications. Users need ad-hoc access to this data to list items in the order in which they ran or the order in which they finished. In addition, users need to calculate the run time of each item, and to do so, they can use the DATEDIFF() system function if they have datetime values to pass in to the DATEDIFF() function. For this example, I'm going to focus on the users' needs and access to the data.
Users need real datetime values. To convert formatted integers to real datetime values, you need to combine the StartDate and StartTime into one datetime value and the EndDate and EndTime into another datetime value. To create the real datetime values for the start of the item and the end time of the item, you can use a UDF. For this function, you can pass in the formatted integer values representing the date and the time, break them down into their individual components for year, month, day, hour, minutes, and seconds, then piece them back together in the correct mdy input format. The formatted integer that stores time uses the 24-hour format, so you don't need to add AM or PM to the string; SQL Server understands times that use either the 24-hour format or AM and PM. Additionally, you can use a large character string (nvarchar(30)) as the return data type so that users can set the format for the final value instead of having to convert it again to change the style. Because display is the most important user need for this datetime data, allowing a large string is helpful. However, when you need to use this string for other purposes (e.g., ordering the data by date), you need to convert it back to a datetime value. Luckily, you won't need to complete this type of conversion very often because many functions such as DATEDIFF() and DATEADD() allow a string as input.
In this case, returning a string gives you flexibility in format and a real datetime value that you can use for input into most system functions. Users can choose any format style for display, but if they want to use this output as input to another datetime function, they must choose a format that's based on their session setting for DATEFORMAT(). Otherwise, their calculations could produce incorrect results. For more details about dealing with datetime data, see "The Datetime Dilemma," June 2002, InstantDoc ID 25173. Generally, coding the function to return a string makes the function more flexible than if you hard-code one final style into it.
To create this function, let's start with a quick review of the information stored in the ItemsRun table. This table tracks the start and end times for each item by storing the start date separately from the start time and the end date separately from the end time. Several options are available for creating a function to deal with this type of data. You can write one function that brings together all these date values and computes the run time (the lapse time between the start date and the end date). Or, you can write a more flexible function that pieces together the date and time values to create the datetime data value for the StartTime or EndTime columns. When you select the flexible function, you can leave the calculations and additional manipulation up to the users or to another function. I usually opt for the more reusable and more flexible function. In this case, let's piece together the individual dates to create a real datetime value. If you want additional homework fun, you can create a function that calls this article's function to compute the run time.
For the first row in Listing 2, 20020901 is the value for StartDate, and 102745 is the value for StartTime. The format for the date value is yyyymmdd, and the format for the time value is hhmmss. Because you need to concatenate the StartDate and StartTime string values and return a datetime value, you need to follow the client's session settings for DATEFORMAT(). Be aware that if you rely on certain configuration settings, users who change their environment settings might create out-of-range problems or incorrect dates. In all the following examples, I expect the mdy default setting for DATEFORMAT(). In addition, I show examples of what happens when users or applications change the default setting.
Prev. page
1
[2]
3
next page 
