How to display dates in easy to read format with MSSQL

  • My favorite date format is: 12-Nov-2007
  • There is no ambiguity with 12-Nov-2007, Is it 11/12/2007 or 12/11/2007? Which is what?
  • The local decides it, but in an Internet world, local becomes irrelevant.
  • See: How to display dates in easy to read format with MSSQL for other notes

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(REPLACE(CONVERT(VARCHAR(11), trans_date, 106), ' ', '-') AS VARCHAR(13)) AS 'dd-Mmm-yyyy',
  4.        trans_type, amount
  5. FROM trans01;
  6. go
counter     emp_short  trans_date              dd-Mmm-yyyy   trans_type amount
----------- ---------- ----------------------- ------------- ---------- ---------------------
1           000010     2007-05-01 00:00:00     01-May-2007   IN         345.00
11          000010     2007-05-02 00:00:00     02-May-2007   IN         175.00
21          000010     2007-05-03 00:00:00     03-May-2007   OUT        -75.00
31          000010     2007-05-05 00:00:00     05-May-2007   OUT        -100.00
41          000030     2007-05-15 00:00:00     15-May-2007   IN         10000.00
51          000030     2007-05-16 00:00:00     16-May-2007   IN         435.00
61          000030     2007-05-20 00:00:00     20-May-2007   OUT        -125.00
71          000030     2007-05-30 00:00:00     30-May-2007   OUT        -1100.00
81          000110     2007-04-12 00:00:00     12-Apr-2007   OUT        -250.00
91          000110     2007-04-30 00:00:00     30-Apr-2007   OUT        -75.00
101         000110     2007-05-10 00:00:00     10-May-2007   IN         1000.00
111         000190     2007-06-01 00:00:00     01-Jun-2007   IN         99.00
121         000190     2007-06-01 00:00:00     01-Jun-2007   IN         134.00
131         000310     2007-07-01 00:00:00     01-Jul-2007   IN         1156.40
141         000310     2007-07-02 00:00:00     02-Jul-2007   IN         67.00
151         000310     2007-08-01 00:00:00     01-Aug-2007   OUT        -586.00

(16 row(s) affected)
  • One of the benefit of the conversion is that the date changed from: 2007-08-01 00:00:00 to 01-Aug-2007 and the time portion of 00:00:00 was dropped, making it even either to read.
  • Dropping the time portion also avoid the questions, such as: But we were closed at midnight!.