Executive Summary:
The need to show the current date (i.e., month, day, and year) in different formats is quite common. Although you can obtain the current date with Microsoft SQL Server's GETDATE function, getting the returned date in the format you need can take a lot of effort and code. The FormatDate user-defined function (UDF) provides dates in various formats without you having to search the Microsoft SQL Server documentation and write custom code.
|
The need to show the current date (i.e., month, day, and year) in different
formats is quite common. Although you can easily obtain the current date with
the GETDATE function, it returns not only the date but also the time. To extract
just the date values, you need to use various functions, such as DAY, MONTH,
YEAR, and DATEPART. If you want separators such as a slash (/) or hyphen (-)
between the day, month, and year, you first need to use the CONVERT function
to convert the numeric date values to strings. And if you require leading zeroes
in the month and day values, even more code is needed. As you can see, getting
the current date in a simple format can take a lot of effort and code. More
complicated date representations require even more effort and more code.
After reading "Automate Date/Time
Conversions" (March 2007, InstantDoc ID
94954) and the five-part T-SQL Black Belt
series "DATETIME Calculations," which
started in the January 2007 issue (InstantDoc
ID 94487), I realized it was time to use a
more formal approach to my date and time
formatting needs. I decided to create a function that would accept a date and return it
in the format I desired. That way, I wouldn't
have to search the SQL Server documentation and write custom code every time I
needed a date in a different format.
My function, FormatDate, returns the formatted date as a NVARCHAR(100) data
type. It requires two input parameters, as the following syntax shows:
dbo.FormatDate(date,format)
The date parameter specifies the date
and/or time you want to format and the
format parameter species the desired format.
The date parameter value needs to be a
sql_variant data type. If you want the current
date, you can use the GETDATE function
as the date parameter.
The format parameter value needs to be a NVARCHAR(100) data type. To specify
the desired format, you can use the following options. (Any other characters
in the format parameter are ignored and remain in the final result.)
- mm = month
- dd = day
- yyyy or yy = year
- hh = hours
- nn = minutes
- ss = seconds
- ms = milliseconds
- mmmm = long month name (e.g., January)
- mmm = short (three characters) month name (e.g., Jan)
- wdd = long day-of-the-week name (e.g., Monday)
- wd = short (three characters) day-of-the-week name (e.g., Mon)
- AM/PM = AM or PM
- am/pm = am or pm
- A/P = A or P
- a/p = a or p
For example, if you want to format the current system date as mm/dd/yyyy, you'd
specify
SELECT dbo.FormatDate(GETDATE(),'mm/dd/yyyy')
The statement
SELECT dbo.FormatDate(‘1:45 pm 27 Mar 07', mm/dd/yyyy hh:nn:ss')
yields the result 03/27/2007 13:45:00. Note that the time is returned using
a 24-hour clock, which is the default. If you want the time returned using a
12-hour clock, you need to include AM/PM, A/P, am/pm, or a/p. For example, the
statement
SELECT dbo.FormatDate(‘7/1/2007 14:00:30', mm/dd/yyyy hh:nn:ss am/pm')
yields the result 07/01/2007 02:00:30 pm.
Listing 1 shows the FormatDate function.
The code in callout A uses the CONVERT function to convert the sql_variant value
in the data parameter (@d) to a NVARCHAR(100) value. I chose the 109 style in
the conversion to NVARCHAR(100) because it returns the number of milliseconds.
The code then checks the converted value to make sure it's a date or time. If
FormatDate doesn't recognize the value as a date or time, it returns the inputted
date value.
The code in callout B uses the
CHARINDEX system function to determine whether a/p, am/pm, A/P, or AM/PM
is part of the format parameter (@fOUT)
value. If so, the code determines whether the
user is requesting that the value be uppercase
or lowercase and whether it should be a
single character (e.g., a/p) or double character (e.g., am/pm).
The code in callout C uses the
CHARINDEX function to find in the
date parameter the various elements of the
requested format. For example, if you specified mm/dd/yyyy as the format parameter,
the code looks for month, day, and year
values in the date you supplied as the date
parameter. The code uses DATEPART function to extract each element from the date
parameter, then uses the REPLACE system
function to replace the value in the format
parameter with element extracted with
DATEPART (converting it to a character
value of an appropriate length if necessary).
The order of these manipulations is
important. For example, the yyyy replace
operation must be performed before the yy
replace operation to avoid having the year
appear twice. The numeric month and day
replace operations must be performed before
the alphabetic month and day formatting to
minimize the chance of having format values
appearing in month or day names.
You can use the FormatDate function
for a variety of tasks. For example, you can
use it to display the first day of the current
month with the statement
SELECT dbo.FormatDate(GETDATE(),'mm/01/yyyy')
Because /01 isn't a recognized format option, /01 is returned
in the result. So, if October 3, 2007, is the current date, the statement returns
10/01/2007. You can then find the last day of the month by using the DATEADD
system function with this date.
You can use the FormatDate function to display the difference between two times.
For instance, the statement
SELECT dbo.FormatDate(CONVERT(datetime,'5:30 pm')
- CONVERT(datetime,'11:00 am'),'hh hours nn minutes ss seconds')
yields the result 06 hours 30 minutes 00
seconds.
The FormatDate function can be used in stored procedures, views, and other
functions to perform more complicated datetime manipulations. For example, FormatDate
is useful when creating a list from a view. To include the report date, enter
dbo.FormatDate(GETDATE(),'mm/dd/yyyy') AS
ListDate
in the SELECT list. If both the date and time are desired, enter
dbo.FormatDate(GETDATE(),'mm/dd/yyyy hh:nn
A/P')
AS ListDate
in the SELECT list.
I've tested the FormatDate function with varying DATEFIRST and LANGUAGE values
and haven't found any problems. For example, the statements
SET LANGUAGE french
SELECT dbo.FormatDate( ‘06.05.2007','wdd dd mmmm yyyy')
yields the result dimanche 06 mai 2007. It's
assumed that the short values for the months
and days of the week are meaningful in
other languages.
As these examples show, I've found many uses for the FormatDate function. I
hope you will, too. If you prefer to use a Common Language Runtime (CLR) version
of this function, see Itzik Ben-Gan's "Format DATETIME" blog at http://www.sqlmag.com/Article/ArticleID/96411/96411.html.
—Roy Byrd, Principal Consultant, Byrd Associates
See Associated Figure