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/TimeConversions" (March 2007) and the five-part T-SQL Black Belt series "DATETIME Calculations," which started in the January 2007 issue, 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. (See also, "T-SQL Classic Date Functions" and "Time Only").
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.
—Roy Byrd, Principal Consultant, Byrd Associates
