Formatting Dates in SQL Server 2012

Finally, after a long wait, we have “Format” in SQL Server which enable us to format dates. In Oracle we have to_char to format a date to any format we want e.g. to_char(date1, ‘YYYY-MM-DD’). And this is since Oracle 9i (2001). Now in SQL Server 2012 we have the Format function which we can use to format date to any format we want.

It’s quite straight forward to use especially if you know .Net formatting, but a few notes would probably help:

1. Month is M (m is minute). Year is yyyy not YYYY.
2. M is single digit month, MM is double digit.
3. MMM is month short name (e.g. Dec), MMMM is long name e.g. December
4. Day of the week is ddd (Wed) and dddd (Wednesday)
5. hh is 12-hour, HH is 24 hour (h or H for single digit)
6. Milliseconds in fff, microsecond is ff (F is 1/10, FFFF is 1/10000)
   f gives zero, F gives blank
7. Escape character is \

Examples:

select format(getdate(), 'yyyy-MM-dd')
2012-12-26

select format(getdate(), 'MM/dd/yyyy')
12/26/2012

declare @a datetime2 = '2012-12-26 13:30:27.1234'
select format(@a, 'yyyy-MM-dd hh:mm:ss.fff')
2012-12-26 01:30:27.123

declare @a datetime2 = '2012-12-26 13:30:27.0004'
select format(@a, 'yyyy-MM-dd hh:mm:ss.FFF')
2012-12-26 01:30:27

declare @a datetime2 = '2012-12-26 13:30:27.0004'
select format(@a, 'yyyy-MM-dd hh:mm:ss.fff')
2012-12-26 01:30:27.000

select format(getdate(), 'ddd')
Wed

select format(getdate(), 'dddd')
Wednesday

declare @a datetime2 = '2012-12-26 11:00:00'
select format(@a, 'HH:mm\h')
11:00h

Complete formatting pattern is here:
http://msdn.microsoft.com/en-US/library/8kb3ddd4.aspx

Note that we can use standard .Net formatting too, e.g. d, D, g, G:
http://msdn.microsoft.com/en-us/library/az4se3k1.aspx

declare @a datetime2 = '2012-12-26 13:30:27.0004'
select format(@a, 'd')
select format(@a, 'D')
select format(@a, 'g')
select format(@a, 'G')
12/26/2012
Wednesday, December 26, 2012
12/26/2012 1:30 PM
12/26/2012 1:30:27 PM

Advertisements