How to display dates in easy to read format with MSSQL

  • Date formatting and display is a difficult subject.
  • Is 1/2/07 January 2nd, 2007 or is it February 1st, 2007? That will depend on your local/tradition and expectation. If you are US based, then it is January 2nd, 2007. If you are UK based, then it is February 1st, 2007. what about in the internet age?
  • 2-Jan-2007 is not open for debate [as long as you can read English].
  • Tuesday 2-Jan-2007 is even better.
  • See: How to display dates in easy to read format with MSSQL for other options/descriptions.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008

Data used

  1. USE sql911;
  2. go
  3. SELECT counter,emp_short,trans_date,trans_type, amount
  4. FROM trans01;
  5. go
counter     emp_short  trans_date              trans_type amount
----------- ---------- ----------------------- ---------- ---------------------
1           000010     2007-05-01 00:00:00     IN         345.00
11          000010     2007-05-02 00:00:00     IN         175.00
21          000010     2007-05-03 00:00:00     OUT        -75.00
31          000010     2007-05-05 00:00:00     OUT        -100.00
41          000030     2007-05-15 00:00:00     IN         10000.00
51          000030     2007-05-16 00:00:00     IN         435.00
61          000030     2007-05-20 00:00:00     OUT        -125.00
71          000030     2007-05-30 00:00:00     OUT        -1100.00
81          000110     2007-04-12 00:00:00     OUT        -250.00
91          000110     2007-04-30 00:00:00     OUT        -75.00
101         000110     2007-05-10 00:00:00     IN         1000.00
111         000190     2007-06-01 00:00:00     IN         99.00
121         000190     2007-06-01 00:00:00     IN         134.00
131         000310     2007-07-01 00:00:00     IN         1156.40
141         000310     2007-07-02 00:00:00     IN         67.00
151         000310     2007-08-01 00:00:00     OUT        -586.00

(16 row(s) affected)

Formatting the dates for readability

  1. SELECT counter,emp_short,
  2.        trans_date,
  3.        CAST(SUBSTRING(DATENAME(dw,trans_date),1,3) + ' ' +
  4.        CONVERT(VARCHAR(11),trans_date,106) AS VARCHAR(15)) AS 'Transaction',
  5.        trans_type, amount
  6. FROM trans01;
  7. go
counter     emp_short  trans_date              Transaction     trans_type amount
----------- ---------- ----------------------- --------------- ---------- ---------------------
1           000010     2007-05-01 00:00:00     Tue 01 May 2007 IN         345.00
11          000010     2007-05-02 00:00:00     Wed 02 May 2007 IN         175.00
21          000010     2007-05-03 00:00:00     Thu 03 May 2007 OUT        -75.00
31          000010     2007-05-05 00:00:00     Sat 05 May 2007 OUT        -100.00
41          000030     2007-05-15 00:00:00     Tue 15 May 2007 IN         10000.00
51          000030     2007-05-16 00:00:00     Wed 16 May 2007 IN         435.00
61          000030     2007-05-20 00:00:00     Sun 20 May 2007 OUT        -125.00
71          000030     2007-05-30 00:00:00     Wed 30 May 2007 OUT        -1100.00
81          000110     2007-04-12 00:00:00     Thu 12 Apr 2007 OUT        -250.00
91          000110     2007-04-30 00:00:00     Mon 30 Apr 2007 OUT        -75.00
101         000110     2007-05-10 00:00:00     Thu 10 May 2007 IN         1000.00
111         000190     2007-06-01 00:00:00     Fri 01 Jun 2007 IN         99.00
121         000190     2007-06-01 00:00:00     Fri 01 Jun 2007 IN         134.00
131         000310     2007-07-01 00:00:00     Sun 01 Jul 2007 IN         1156.40
141         000310     2007-07-02 00:00:00     Mon 02 Jul 2007 IN         67.00
151         000310     2007-08-01 00:00:00     Wed 01 Aug 2007 OUT        -586.00

(16 row(s) affected)
  • I personally think that the newly formatted date is not only easier to read, but also prevents any error/misunderstanding.
  • datename(dw,trans_date),1,3) returns the first 3 letters of the day of the week.
  • convert(varchar(11),trans_date,106) returns the date in the format dd mmm yyyy.
  • Only the convert allows the country style, in this case 106.
  • Cast does not allow the style type for the date formatting.
  • For a full list of all the formats: Microsoft: CAST and CONVERT