Formatting Dates in SQL Server

Date Formatting is very helpful. While it is not always the preferred thing to do in your database, there is native support built in. SQL Server allows you to convert or cast a date into a string value, and during the casting to provide a formatting method.

The formatting is specified by providing an integer value. Following is a query demonstrating the formatting natively supported in SQL Server at this time.

;WITH Sequence (Number)

AS

(

SELECT TOP (14)

ROW_NUMBER() OVER (PARTITION BY 1 ORDER BY ID) AS Number

FROM SYSOBJECTS

)

SELECT

Number as TwoDigitYearFormat

,CONVERT(VARCHAR(20), GETDATE(), Number) AS TwoYearFormatted

,Number + 100 as FourDigitYearFormat

,CONVERT(VARCHAR(20), GETDATE(), Number + 100) AS FourYearFormatted

FROM Sequence

Executing the query results in the following output.

I prefer the Dot Net formatting implementation. A DateTime data type can be converted to a string using the ToString() method. You can provide a string format in the ToString methods allowing you to specify each of the individual date components.

If you have a DateTime variable MyDate with the value “1/23/2015 01:23:04:461 PM” you could convert it to the string 23/jan/2015 13:23:04:461 by using the following format:

MyDate.ToString(“dd/MMM/yyyy HH:mm:ss:fff”)

I wish they supported the same kind of formatting in SQL Server. Perhaps that is a place for you to add a CLR function. Then you have a granular method to format any date without having to resort to a lot of DATEPART SQL functions, etc.

I know I’ve written on this before. I find the topic to still be fresh, as I often have people ask me how this kind of formatting may be done.

Do you have a TSQL function or CLR function you use for formatting dates in SQL? Share your idea in a comment or by Email to btaylor@sswug.org.

Cheers,

Ben