Background:
MS SQL Server gives a limited range of formatting
dates. This range depends on converting
data types by assigning a numbered style rather then a character
representation.
CONVERT ( data_type [ ( length )
] , expression [ , style ] )
CAST
and CONVERT (msdn link)
Another option that SQL Server exposes is the SET DATEFORMAT
property.
SET DATEFORMAT { format | @format_var }
SET
DATEFORMAT (msdn link)
This format is friendlier; in that you can actually tell
what format it is trying to set just by looking at it. Valid parameters include mdy, dmy, ymd, ydm,
myd, and dym. The U.S. English default is mdy.
Goal:
My goal is to create a dynamic method of defining the format
of a date that is common with other programs.
The code must be available within a function for use within SELECT
statements. The code must be small and
quick.
Defining the format:
For this exercise, I looked at the date picker control that
Microsoft offers. Here are a few links
to the MSDN library to help get you started:
FORMAT_STRING
Contents
OutputFormat
Property
Date Time
String Transformation
From these pages, and many others, I identified to most
basic formats and compiled a list.
|
D
|
The one-digit of the two-digit day.
|
|
DD
|
The two-digit day.
A zero precedes single-digit day values.
|
|
DDD
|
The three-character weekday abbreviation.
|
|
DDDD
|
The full weekday name.
|
|
H
|
The one-digit or the two-digit hour in 12-hour format.
|
|
HH
|
The two-digit hour in 12-hour format. A zero precedes single-digit values.
|
|
HHH
|
The one-digit or the two-digit hour in 24-hour format.
|
|
HHHH
|
The two-digit hour in 24-hour format. A zero precedes single-digit values.
|
|
N
|
The one-digit or the two-digit minute.
|
|
NN
|
The two-digit minute.
A zero precedes single-digit values.
|
|
M
|
The one-digit or the two-digit month number.
|
|
MM
|
The two-digit month number. A zero precedes single-digit values.
|
|
MMM
|
The three-character month abbreviation.
|
|
MMMM
|
The full month name.
|
|
T
|
The one-letter A.M. and P.M. Abbreviation (That is, “AM”
is displayed as “A”)
|
|
TT
|
The two-letter A.M. and P.M. abbreviation (that is, “AM”
is displayed as “AM”)
|
|
Y
|
The year is displayed as the last two digits, but with no
leading zero for any year that is less than 10.
|
|
YY
|
The last two digits for the year. For example, 1998 would be displayed as
“98”
|
|
YYY
|
The full year. For
example, 1998 would be displayed as “1998”.
|
I found it hard to work with some of the formats because
most installations of SQL Server to not recognize case-sensitivity. In this
case, I simply added more letters. For
example, a 12-hour format would be a lower-case “H”, where as a 24 hour format
would be an upper case “H”. For the
24-hour representation, I added more “H” characters.
Retrieving Values:
The next problem I tackled was populating the values with
the date provided. I would populate the
day, the hour, the year, and so on. I
was ignoring what the format wanted to be passed back. Most values were obtained by using the
DATEPART function. Rather then storing
the values as numbers, I “Casted” them as VarChars. This allowed me to continue playing with them as strings and to
use them in string related functions.
One of the tricks I used most was the RIGHT function to get two-digit
numbers preceded with zeros.
SET @d =
CAST(DATEPART(d, @Date) AS VARCHAR(2))
SET @dd =
RIGHT('0' + @d, 2)
Another function I used was the DATENAME to grab the name of
the month. For abbreviations of the
month, I selected the LEFT 3 characters.
SET @MMMM =
DATENAME(m, @Date)
SET @MMM =
LEFT(@MMMM, 3)
Preparing the Format:
The format string has the ability to contain other
characters then the name of the month, hour, day, etc. For example, you may want to format the time
as “HH:NN” with a colon in between. To
support this, I replace each recognized format with a temporary place holder.
SET @Format
= REPLACE(@Format, 'dddd', '\\\\1\\\\')
SET @Format
= REPLACE(@Format, 'ddd', '\\\\2\\\\')
SET @Format
= REPLACE(@Format, 'dd', '\\\\3\\\\')
SET @Format
= REPLACE(@Format, 'd', '\\\\4\\\\')
I began with the largest repetitive characters first, and
worked my way down. The reason these
place holders are needed is due to the fact that some months, may contain
recognized formats. Take the month of
“May” for example. The letter “M” is
recognized to be a format for a single and two-digit number of the month. If I were to replace the “mmmm” format
first, and then apply the “m” format -- I would end up with “5ay”.
Applying the Format:
Applying was much quicker and appeared like so:
SET @Format
= REPLACE(@Format, '\\\\1\\\\', @dddd)
SET @Format
= REPLACE(@Format, '\\\\2\\\\', @ddd)
SET @Format
= REPLACE(@Format, '\\\\3\\\\', @dd)
SET @Format
= REPLACE(@Format, '\\\\4\\\\', @d)
I simply reversed the process and replace the placement
markers with the values I retrieved earlier.
In the end, all that was left to do was return the format
passed –
return(@Format)
Next, I created a test to see if it would work
PRINT
dbo.FormatDate(GETDATE(), ‘MM/DD/YY HH:NN TT’)
And the result …
05/22/2002
07:15 PM
This sums up my adventure with T-SQL programming. If you find this information useful, please
let others know. Personal information
about myself can be found on my personal website at http://www.lewismoten.com
| 
Download article