SQL Server Magazine

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.

   Prev. page   [1] 2 3     next page