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 (http://www.sqlmag.com, InstantDoc ID 94954) 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.