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:
- Month is M (m is minute). Year is yyyy not YYYY.
- M is single digit month, MM is double digit.
- MMM is month short name (e.g. Dec), MMMM is long name e.g. December
- Day of the week is ddd (Wed) and dddd (Wednesday)
- hh is 12-hour, HH is 24 hour (h or H for single digit)
- Milliseconds in fff, microsecond is ff (F is 1/10, FFFF is 1/10000)
f gives zero, F gives blank - 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
Leave a Reply