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.)