Output Date Formatting

In this post, Data Education founder and trainer Adam Machanic continues his discussion on the CONVERT function. Here, he explains that the only difference is that the function is converting an instance from a date/time to a string rather than the other way around.

The CONVERT function is not only useful for specification of input date/time string formats. A much more common use for it is formatting dates for output.

Before continuing, I feel that a quick disclaimer is in order: It’s probably not a good idea to do formatting work in the database. By formatting dates into strings in the data layer, you may reduce the ease with which stored procedures can be reused. This is because such formatting may force applications that require differing date/time formats to convert the strings back into native date/time objects, and then re-format them as strings again. This additional work on the part of the application is probably unnecessary, and there are very few occasions in which it really makes sense to send dates back to an application formatted as strings. The main example that springs to mind is when doing data binding to a grid or other object that doesn’t support the date format you need—but of course, that is a rare situation.

Just like when working with input formatting, the main T-SQL function used for date/time output formatting is CONVERT. The same set of styles that can be used for input can also be used for output formats; the only difference is that the function is converting from an instance of a date/time type into a string, rather than the other way around. The following T-SQL shows how to format the current date as a string in both U.S. and British/French styles:

--British/French style
SELECT CONVERT(VARCHAR(50), GETDATE(), 103)
--U.S. style
SELECT CONVERT(VARCHAR(50), GETDATE(), 101)

The set of styles available for the CONVERT function is somewhat limited, and may not be enough for all situations. In addition, those coming from an Oracle background often complain of the lack of a function similar to Oracle’s TO_CHAR in SQL Server.

SQL Server 2005’s CLR integration provides a solution to these problems. .NET’s System.DateTime class includes extremely flexible string formatting capabilities, which can be harnessed using a CLR scalar user-defined function. The following method exposes the necessary functionality:

public static SqlString FormatDate(
    SqlDateTime Date,
    SqlString FormatString)
{
    DateTime theDate = Date.Value;
    return new SqlString(theDate.ToString(FormatString.ToString()));
}

This UDF converts the SqlDateTime instance into an instance of System.DateTime, then uses the overloaded ToString method to format the date/time as a string. The method accepts a wide array of formatting directives, all of which are fully documented in the Microsoft MSDN Library. As a quick example, the following invocation of the method formats the current date/time with the month part first, followed by a four-digit year, and finally the day:

SELECT dbo.FormatDate(GETDATE(), 'MM yyyy dd')

Keep in mind that the ToString method’s formatting overload is case-sensitive. MM, for instance, is not the same as mm, and you may get unexpected results if you are not careful.

1 Comment

  1. VicVicMar 3, 2012
    12:38 PM

    Yesterday I was attaching a dtbaaase but SQL Server is throwing out an error (compression issue). I asked for help at twitter, and the tweeps sent me to various online resources (I focused on Paul Randal’s blog). Research, read, Research, read test research read test I was running out of clues!Then I came across a forum post that suggests I check the data file’s properties and see if it is marked Compress contents to save disk space (I unticked it). That solved the issue. I ran DBCC CHECKDB and the dtbaaase was ok! Talking about overlooking