Data Warehousing and Business Intelligence

26 December 2012

Formatting Dates in SQL Server 2012

Filed under: SQL Server — Vincent Rainardi @ 1:46 pm

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

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: