User-defined scalar functions (scalar UDFs) are tools that can simplify access to real and contrived date and time data for the end user. In my September TSQL-Tutor article "Datetime for Durable Databases," InstantDoc ID 26170, I demonstrated how to put together a simple scalar UDF that you can use with datetime values. Now, let's look at the ways you can use UDFs to handle more complex date and time data. One opportunity for using a UDF arises when your data is a date and time value but it isn't in datetime format. For example, what if you had to handle a data dump from a system in which all date values are sent as the number of seconds past midnight of a system-defined date such as January 1, 1970? To test the syntax for this problem's examples, you can download the UserDefinedFunctionExamples.sql commented script file at http://www.tsqlsolutions.com, InstantDoc ID 26538.
The first thing you notice when you begin to develop a solution to this type of problem is that the date data is an integer that you must convert to a datetime data type—a relatively easy task because a function that handles this exact problem already exists: DATEADD(). To return the proper date and time, you could use the following statement:
SELECT DATEADD(ss, IntegerValue, BaseDate)
Adding this expression to all your queries doesn't seem like much additional work. However, you can minimize your typing and make your queries easier to use by creating a scalar function that automatically supplies input parameters. Let's use a function called PhoneSystemDate() and supply only one parameter for the input—the integer that needs to be converted to real time. This function returns the current date and time as a datetime value from the supplied integer. For the body of the function, use the DATEADD() function, specifying the component you want to add (seconds, represented as ss) and the base date you want to add this component to (January 1, 1970). Listing 1 creates the PhoneSystemDate scalar UDF. The following three statements are ways you can use the PhoneSystemDate() function. Remember, as I discussed in "Datetime for Durable Databases," you must owner-qualify functions when you use them.
SELECT dbo.PhoneSystemDate(981419317)
SELECT dbo.PhoneSystemDate(981419454)
SELECT dbo.PhoneSystemDate(981419537)
GO
Instead of receiving the date value as an integer, you might receive a date from a formatted string. Other formats used for date and time data relate to formatted strings (numeric values, as we've just discussed). The formatted value can be a character string (e.g., "Jan102002" for January 10, 2002) or a formatted integer (e.g., 20021012 stored in a 4-byte integer column for October 12, 2002). Just as you might need a current date value when an integer is supplied, you might find formatted character strings or formatted numeric values when you need to return a current date value. In some tables, the date and time components reside in one field; in other tables, they're separated into two or more fields. If you need to use these datetime values with datetime functions and also need the code to perform datetime math on the datetime values (e.g., you want to add days or weeks to an existing date), you need to convert them to a datetime datatype first. For example, you might choose to store only the date portion of a datetime value in a database. That database could store September 14, 2002, as the formatted character string 09142002 or as the character string or integer 20020914. You can convert each of these formats into a real datetime value, then pass the converted date value to other datetime functions. In fact, in tables that require both date and time components, you'll often find the date and time components separated and stored as integers. For example, an application can internally use the date and time data more easily when it controls the date values as numeric values. That is, storing the components as integers often gives the application the flexibility to look at times within a day without having to constantly strip out the date component. However, when you choose T-SQL to create reports and access the data, you might need to convert the values back to real datetime values so that you can manipulate the display or perform datetime calculations. Let's work through one of the more difficult scenarios for converting integers to real dates, and I'll leave the date-only character string up to you as homework. (See the T-SQL Project sidebar for your homework assignment.)
When date and time values are stored as integers, you need to convert each value to a string, use a substring to parse the value for the proper components, then reassemble the value as a string that's formatted appropriately as datetime input. You base these steps on the expected format for the DATEFORMAT() function's session setting. If you don't know the client setting for DATEFORMAT(), or if you don't know whether users typically change this setting, you might have a problem. In fact, you can't change session settings within UDFs, so you have to decide whether a function would work more effectively than a stored procedure (which could guarantee the session settings by changing them within the code). However, if users who access the database often change their session settings, using a stored procedure also might be a concern. When session settings aren't consistent, excessive stored-procedure compilations—which can compromise performance—might result. To solve this problem, you could write a stored procedure that changes the session setting to call the UDF. Although consistency is guaranteed, performance might be slowed. In general, I recommend that to achieve consistency in client applications, you should avoid changing session settings. Use the easiest method to achieve your goal. In this case, I recommend that the UDF should use the default setting for DATEFORMAT(). Then you can call the UDF from within a stored procedure to guarantee consistent and accurate use. If you can guarantee consistency from your client applications, you can skip creating the stored procedure.
Prev. page
[1]
2
3
next page 
