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.
Prev. page
1
[2]
3
next page 
