Display Dates in the Format You Need

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.

Advertisement

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

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

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

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.

I contacted the author, Roy Byrd, about your question. He noted that,
"The problem is the format parameter 'hh:mm:ss' uses the 'mm' instead of the 'nn' for the minutes value. The 'mm' in the format paramter is for months. The correct format is 'hh:nn:ss' if minutes are desired.
The following script
declare @Datefield datetime
set @Datefield='10/9/2007 2:27:45'
select dbo.FormatDate(@Datefield,'hh:mm:ss')
returns 02:10:45 showing the value of the month is 10 in the 'mm' position. If the format is changed to use 'nn' in the minutes position, 02:27:45 is returned.
The T-Sql statement
SELECT CONVERT(varchar(10), @DateField, 108)
returns 02:27:45."
I hope that answers your question. If not, please let me know.
Karen Bemowski
Senior editor
SQL Server Magazine, Windows IT Pro, Scripting Pro VIP

From the Blogs

Many organizations today cannot use public cloud solutions because of security concerns, administrative challenges and functional limitations. However, they still need a centralized platform where end users can conduct self-service analytics in an IT-enabled environment....More

It is crucial to move away from data and analytics stored on individual desktop computers. Today’s solutions must promote holistic, collective intelligence. The strong, continued alliance between Microsoft and Pyramid Analytics helps make all this possible....More

To become a truly data-driven enterprise, many business leaders recognize that they must extend the capabilities of self-service business intelligence (BI) and analytics to more of their business users. Many BI tools tackle part of this need, but they don’t offer a complete enterprise solution....More