| Downloads |
|---|
| 26170.zip |
Because most T-SQL Tutor readers design applications from the ground up, the two previous T-SQL Tutor columns about datetime data ("The Datetime Dilemma," June 2002, InstantDoc ID 25173, and "Manipulate and Calculate," July 2002, InstantDoc ID 25433) focused on properly storing date and time data. However, I'm dedicating this column to those readers who work with an existing database whose design and schema are set. When you can't alter a database's schema, you must learn to work within the bounds of the existing structures. Many systems optimize their date and time data based on application usage such as internal computations and not on user queries. In many cases, this kind of optimization results in unusual date formats (e.g., formatted character strings, formatted integers, separate formatted columns for date versus time, integers representing a unit of time past an arbitrary date) that describe a specific date and time. All these date formats are acceptable ways of describing a date and time—for the person who wrote the application—but if developers and users have other purposes for the data and need different formats for display and manipulation, developers have to convert the formats and make them easier to use. Enter user-defined scalar functions (scalar UDFs), the tools that simplify access to real and contrived date and time data for the end user. To test the syntax for this column's examples, you can download the UserDefinedFunctionExamples.sql commented script file at http://www.tsqlsolutions.com, InstantDoc ID 26170.
The Basics of Scalar UDFs
UDFs are new to SQL Server 2000, but the range of their usefulness will only keep expanding. In future releases, you can expect to write your UDFs in languages other than T-SQL (e.g., in C# or any language supported by the Common Language Runtime—CLR). For now, scalar UDFs are similar to system functions in a couple of ways. Both functions always return only one value (so they tend to encapsulate simple expressions). Also, you can use and apply them in queries, WHERE clauses, and constraints, so their code should be simple to keep processing time short. However, you can program UDFs yourself. If you use them against large result sets or if they become complex, make sure you test them well for acceptable performance (as defined by your application). After you start writing scalar UDFs, you'll probably find yourself inventing all sorts of ways to use them.
In the June and July 2002 T-SQL Tutor columns, I discussed formatting date and time data by using the CONVERT() function's style parameter. When you use CONVERT(), you can change your datetime data to one of many styles—date alone, time alone, and month, day, year, and time in various orders and formats. Although numerous styles are available, you might want to devise a style of your own. In SQL Server 7.0 and earlier, if you need only a customized separator, for example, you have to write your complex expression and formatting style each time you need that format. Or, you can create a view that's reusable. Either way, you need to rewrite the entire query or create another view every time you want to use your custom format for another column or with a different separator. However, in SQL Server 2000, you create a scalar UDF to encapsulate your complex expressions and use it in places where a view falls short—such as in the SELECT list and the WHERE clause. Let's look at a specific case.
In a newsgroup forum, a reader asked how to customize the separator between the month, day, and year components of a date. First, let's devise a pre-SQL Server 2000 solution to the reader's problem. To begin, choose the GETDATE() function to format your target date. If today's date is September 14, 2002, and you want to display the data by using SQL Server's built-in datetime functions, you might use CONVERT() with a style of 101 to display the date as 09/14/2002. The following code uses the GETDATE() function to display the data in this style:
SELECT CONVERT(char(10), getdate(), 101)
However, if you want to use colons (:) to separate the date components, you need to piece the individual components together from a series of datetime function calls. That is, you need to pull out the month component, insert a colon, pull out the day component, insert a colon, then finally pull out the year component. To extract the numeric values for each of these components, you could start by using the DATEPART() function or the ANSI-standard DAY(), MONTH(), and YEAR() functions. For example, to return the three components from the current date, you could write the queries that Listing 1 shows. Then, you could try to use the plus symbol (+) as the string concatenator to add together each of these components and the separators (the colons), as Listing 2 shows. However, if you try to execute either of the queries that Listing 2 shows, you'll receive an error.
The problem with the two expressions in Listing 2 is that you're trying to add numbers and string values together. If the string values were numbers, they would be implicitly converted to numbers (e.g., '12' + 45 would successfully yield 57, but not 1245). However, in this case, the colon isn't a number and can't be implicitly converted. To successfully produce the desired result, each of the components you're adding together must already be a string. In our example, you must convert to a string each of the numeric values that the system functions return. Then, you can concatenate the values by using the colons.
Before you convert the three numeric values, let's briefly evaluate other datetime functions to make sure that you're using the most efficient function for the job. You can use the DATEPART() function and the DATENAME() function, which returns a string for each component—even components that always return a number. (Many developers overlook this function unless they want the month value to be spelled out.) For example, DATENAME() returns September for the month, which in this case isn't the format you want. However, for the day and year components, DATENAME() returns exactly the same values as DATEPART(), but it returns them as a string. So if you change the day and year requests from DATEPART() to DATENAME() and use CONVERT() for the month, the query will return the date in the format you want, and you'll need to make fewer conversions. The following formula:
SELECT CONVERT(varchar(2), DATEPART(month, getdate())) + ':' + DATENAME(day, getdate()) + ':' + DATENAME(year, getdate())
returns 9:14:2002. However, this format isn't quite perfect. Month and day components vary by how many digits are in the day and month values, so this formula returns date values whose components look inconsistent and misaligned. For example, September 1, 2002, returns 9:1:2002 and December 12, 2002 returns 12:12:2002.
In a large result set, one column that contains different dates that vary in length and don't line up is hard to read. In general, you should return the month and day components as two digits: September as 09, January as 01, the fifth day of the month as 05. So your next step is to make all components two digits regardless of the numeric value returned. A simple trick is to add a leading zero to every month or day component (regardless of value), then take only the right two characters of the expression. For example, when you add a leading 0 to 9 for September, the result is 09. Then, when you take the right two characters of 09, the code returns 09. For October, when you add the leading 0 to 10, then take the right two characters of 010, the result is 10. So, no matter what month or day value you use, this formula always returns correctly aligned dates. To make this solution work in your query, you add 0 as a string to each of the components, then use the RIGHT() function to take the right two characters:
SELECT RIGHT('0' + CONVERT(varchar(2),
DATEPART(month, getdate())), 2) + ':' +
RIGHT('0' + DATENAME(day, getdate()),
2) + ':' + DATENAME(year, getdate())
Finally, you've retrieved the date in the format you're looking for. To use this solution for a table column, you can substitute the GETDATE() function calls with a column name. For example, to return the book title and its publication date from the titles table in Pubs, you can use the query that Listing 3 shows.
What's wrong with this solution? Every time you need to change the date format, you have to type in this complex expression. As an alternative, you might choose to create a view to hide the expression from the user. However, you don't get simplified access to this datetime expression (except when using the view), and you need a view for each separator that you want to use. Therefore, using the only solution that was available before the release of SQL Server 2000 doesn't scale well; it's time-consuming and hard to manage.
Instead, you can use SQL Server 2000's scalar UDF to solve the problem. In this article's example, you want to take a date, pull out its components, then customize the separator. To make this UDF reusable for different dates and separators, the function should have two input parameters—the date value and the separator. When the function has been assigned two parameters, you can use it with any single-character separator and still be flexible enough to support a column name as input for the date value. Remember, a scalar UDF can have only one output, yet it can have many input parameters. Let's take the following steps to create this function:
- Name the function.
- Define the function's input parameters and their individual data types.
- Define the single datatype value that the query will return.
- Use the expression you created in Listing 3 as the main body of the code.
The syntax for creating a scalar UDF is fairly straightforward, as the following pseudocode shows:
CREATE FUNCTION FunctionName ( InputParam1 datatype = DefaultValue, InputParam2...) RETURNS datatype AS BEGIN RETURN (expression) END
For the solution to this example, you need two input parameters—a datetime value and a separator (defaulting to a colon for ease of use)—a string of 10 characters to be returned, and the expression you worked out in Listing 3. Listing 4 shows the resulting function.
Note that the code explicitly owner-qualifies this function. Although you don't need to owner-qualify on creation, you must owner-qualify on use. Because you can use functions as part of your expression in the SELECT list, Microsoft decided to preventatively minimize collisions with the company's future built-in functions. For example, if you created a function named Median, then Microsoft released a system function called Median, you would experience namespace collisions. Therefore, you must explicitly owner-qualify your UDFs to guarantee that you'll always return your function's value instead of any future built-in function's value. Also, owner-qualifying on execution—whether you use a function, stored procedure, or view—is always good practice. Owner-qualifying can lead to better performance because you waste less time on lookups that fail because the owner is ambiguous.
After you've created the function, you can flexibly use it where you used to enter the complex expression you created for SQL Server 7.0 and earlier releases. You can use the three examples in Listing 5 to test the new function. In these examples, notice how the DEFAULT value was requested for the second parameter. Although you don't have to furnish parameters when you use stored procedures, you must explicitly supply parameters for functions, using placeholders of DEFAULT for default values. The code in Listing 5 shows a few ways that you can use this function. The first example uses the current date as input and the default value for the separator. The second example uses an exclamation mark as the separator along with the current date. The last example, which uses values from the pubdate column of the Pubs titles table, produces a result set in which the dates are formatted with colons.
Scalar UDFs give you a way to encapsulate simple expressions and easily reuse them. These functions are similar to stored procedures in their code, yet their usage is identical to that of system functions. In an upcoming column, I'll continue to explore the manipulation of datetime data by showing you more examples of scalar UDFs.
