Formatting Date Output in SQL

Dave

Formatting dates for output is quite easy in SQL using the CONVERT method.  The hardest part is remembering the code to use so you get the format you require. Below is an example of each format available (taken from MSSQL)

SELECT GETDATE() -- 2012-05-11 12:15:17.813
SELECT CONVERT(VARCHAR(30), GETDATE(), 100) -- May 11 2012 12:15PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 101) -- 05/11/2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 102) -- 2012.05.11
SELECT CONVERT(VARCHAR(30), GETDATE(), 103) -- 11/05/2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 104) -- 11.05.2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 105) -- 11-05-2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 106) -- 11 May 2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 107) -- May 11, 2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 108) -- 12:15:17
SELECT CONVERT(VARCHAR(30), GETDATE(), 109) -- May 11 2012 12:15:17:813PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 110) -- 05-11-2012
SELECT CONVERT(VARCHAR(30), GETDATE(), 111) -- 2012/05/11
SELECT CONVERT(VARCHAR(30), GETDATE(), 112) -- 20120511
SELECT CONVERT(VARCHAR(30), GETDATE(), 113) -- 11 May 2012 12:15:17:813
SELECT CONVERT(VARCHAR(30), GETDATE(), 114) -- 12:15:17:813

To change the year value from 4 (yyyy) digits to 2 digits (yy) deduct 100 from the style identifier.

SELECT CONVERT(VARCHAR(30), GETDATE(), 1) -- 05/14/12

This does not change all formats as some dates would be confusing to read if the year part was just the 2 digits so it defaults to 4 digits year.

SELECT CONVERT(VARCHAR(30), GETDATE(), 0) -- May 14 2012 12:25PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 9) -- May 14 2012 12:25:20:993PM
SELECT CONVERT(VARCHAR(30), GETDATE(), 13) -- 14 May 2012 12:25:20:993

It is also worth noting that normal UNION rules apply even though a style has be specified. If you union the previous statements you will now get the following.

SELECT CONVERT(VARCHAR(30), GETDATE(), 100) -- 2012-05-11 12:17:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 101) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 102) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 103) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 104) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 105) -- 2012-11-05 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 106) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 107) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 108) -- 1900-01-01 12:17:23.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 109) -- 2012-05-11 12:17:23.200
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 110) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 111) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 112) -- 2012-05-11 00:00:00.000
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 113) -- 2012-05-11 12:17:23.200
UNION SELECT CONVERT(VARCHAR(30), GETDATE(), 114) -- 1900-01-01 12:17:23.200
UNION SELECT GETDATE() -- 2012-05-11 12:17:23.2

The last thing to note is there may be localised settings that which influence how dates are formatted.

Leave a Reply