SQL Server offers two native data types for storing date and time data: datetime and smalldatetime. Both store the date and time together, with datetime offering more range and precision than smalldatetime. Table 1, compares these two data types.
Because SQL Server stores date and time information together, working with date and time data—even with the help of built-in T-SQL conversion functions—is challenging. Storing date and time information in the same column means you have to parse the date information if you want to separate it into individual elements for data input, date math, or data retrieval.
T-SQL was designed as a data-manipulation language and doesn't have rich formatting capabilities. Ideally, you should handle date formatting at the client. For example, SQL Server 2005's common language runtime (CLR) integration lets you build solutions that use client APIs and Microsoft .NET programming languages, which have simple, functional tools for such tasks. But if your organization is one of the many still running SQL Server 2000, you can use T-SQL to build a flexible, reusable solution for converting date/time data into the various formats you need. Let's briefly look at some date/time-conversion examples, then see how you can auto-mate such conversions by building an extensible, data-driven utility based on a table and a simple stored procedure.
User-Friendly Conversions
Suppose you want to display the date 06/01/2007 as the nicely formatted string Friday, June 1, 2007. You would start with a date/time pair that looks like one of the following:
Datetime:
2007-06-01 00:00:00.000
Smalldatetime:
2007-06-01 00:00:00
These data types are accurate and precise but not very user friendly. To complicate matters, SQL Server doesn't offer a formatting function that will produce the desired result. Instead, you need to construct a concatenated expression, such as the one that Listing 1 shows. If you run this code in Query Analyzer (assuming the login language is us_english), you'll get the output you're looking for: Friday, June 1, 2007.
Now let's say you want to find out how many hours, minutes, and seconds have passed between two specified dates. If you aren't fussy about the output, you could get the results through a single expression that uses T-SQL's CONVERT() function, as the code in Listing 2 shows. This example returns the time difference as 18:30:00—a result that might be a little cryptic to users. Moreover, unless you have a photographic memory, every time you use the CONVERT() function, you'll need to look up the style code that produces the correctly formatted output. In Listing 2, style code 108 tells the function to return the results in hh:mm:ss format. To get more userfriendly output, you can expand on the CONVERT() function's results, using the SUBSTRING() function to concatenate the words Hours, Minutes, and Seconds to their respective time elements, as Listing 3 shows. This code returns the result as 18 Hours 30 Minutes 00 Seconds.
Writing this type of code once or twice might be mildly entertaining, but after the umpteenth conversion routine, you start to wonder whether there's an easier way to manage date formatting than writing custom routines and memorizing style codes. The solution needs to be reusable, data-driven, and easy to use.
When deciding whether to automate a process, I ask myself two questions: How often will I use the automated solution, and will the time spent creating the solution pay off with adequate ROI? In the case of date/ time conversion and formatting, the answer to the first question is "a lot." If you're like me, you inevitably need either the date or the time, but seldom both at once, and seemingly always in a format that involves writing a custom expression. In addition, a data-driven, reusable solution to flexible date/time formatting is quite straightforward, so it's well worth your time. By creating a simple SQL Server table and a stored procedure, you can hand most date-formatting logic back to the application layer and free up your calendar for less mundane tasks.
Storing Date Formatting Codes
Because you typically want to convert a date that's supplied in one format into a different format for display or output, you need to set up a table that contains date-for matting instructions. Run the code that Listing 4 shows to create and populate a sample table called dt_codes.
Dt_codes contains reference values—such as the style code and style text—that the stored procedure uses to perform the date/ time conversion. Table 2 shows these reference values. You can use the dt_codes table to hold both input and output instructions. So instead of memorizing style codes, you can use a simple, intuitive expression such as ‘yymmdd' to pass formatting instructions to the stored procedure. The mssql column holds a True or False (bit) value; when the value is True, the conversion is handled by SQL Server's native CONVERT() function instead of a custom expression. (We'll look at custom expressions in a moment.)
Processing Formatting Codes
Now that you’ve created the table to store the formatting codes, you need to write the stored procedure that performs the conversion. Web Listing 1 shows the sp_format_dt stored procedure, which accepts three parameters: a date, a dateinput format, and a desired output format. To simplify input, all three parameters are initially defined as the varchar data type. The following statement shows how you might execute the stored procedure:
exec sp_format_dt ‘05/10/2008’, ‘mm/dd/yyyy’,’yyyy-mm-dd’
Executed with these parameters, the stored procedure returns the date formatted as 2008-05-10T00:00:00.
Notice that the output—which contains unwanted hours, minutes, and seconds— doesn’t exactly match the requested format of yyyy-mm-dd. This result is an international ISO8601 format (style code 126), which represents the closest match to the desired output. To get the result you want, you need to eliminate the time portion by replacing the CONVERT() function in the stored procedure with a custom expression.
Extending and Customizing Date/Time Formats
In the preceding example, you couldn’t exactly match the requested output format. To solve the problem, you need to modify the dt_codes table to contain the values that Table 3 shows.
First, change row 7 in the dt_codes table so that the style_text column includes hours, minutes, and seconds. Then, add a new row, row 8, that contains the date-only output yyyy-mm-dd and a dt_function value of CUSTOM. The CUSTOM value specifies that the date/time conversion or formatting code that the stored procedure should call is in a custom expression. Row 8 also sets the mssql flag to False, indicating you don’t use a native T-SQL function to format the output.
The dt_codes table’s dt_function column is for reference only; the stored procedure actually uses the style_code column to determine the output format. In row 8, the style_code is 200, which is outside the range of style codes available to the CONVERT() function.
Now, rerun the stored procedure using the same parameters as before:
exec sp_format_dt ‘05/10/2008’, ‘mm/dd/yyyy’,’yyyy-mm-dd’
This time, instead of invoking the CONVERT() function, the stored procedure passes the parameters to a user-defined expression, which produces the desired yyyy-mm-dd, date-only format: 2008-05-10. (Note that T-SQL’s DATEFORMAT setting determines how SQL Server interprets character strings as it converts them to date values. This article’s examples assume the DATEFORMAT setting is the English default, mdy. A different DATEFORMAT setting will yield different results.)
The user-defined expression simply trims the CONVERT() function’s output (style code 126) to a shortened, date-only version, as callout A in Web Listing 1 shows. You can easily add your own custom expressions to the stored procedure to produce virtually any date/time format you need. Just remember that every row in the dt_codes table needs a corresponding expression in the sp_format_dt stored procedure. For custom expressions, use stylecode numbers outside the range of the built-in CONVERT codes (100-131). As you add expressions, you might want to use the SELECT CASE construct instead of a series of IF … ELSE IF statements. Either construct works, but SELECT CASE more efficiently handles multiple options of the same type. In your production implementation, also remember to add some error trapping in the stored procedure to improve fault tolerance.
A Worthwhile Investment
After you incorporate your own custom touches, you’ll have a production-ready utility to add to your T-SQL toolbox. The stored procedure’s input parameters are simple and more intuitive than a numeric style code. And the extensible, table-driven design lets you add new date/time formats as you need them. In a production environment, user-friendly formatting of data elements isn’t just desirable; it’s typically required for the UI and printed reports. Your one-time investment in writing the code to produce various output formats will pay off in increased efficiency as you continue to find uses for this little utility.
