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.