SQL: CONVERT style examples when converting Date/Time to String

The script below can be used to obtain an example of each of the different styles that can be output using the CONVERT Transact-SQL statement. I have output the styles including the century, but the script can easily be modified to output examples without.

DECLARE @Date DATETIME
SET @Date = GETDATE()

SELECT '100' AS [CONVERT style], 'Default' AS Standard, 'mon dd yyyy hh:miAM (or PM)' AS Format, CONVERT(VARCHAR(100), @Date, 100) AS Example
UNION SELECT '101', 'U.S.', 'mm/dd/yyyy', CONVERT(VARCHAR(100), @Date, 101)
UNION SELECT '102', 'ANSI', 'yyyy.mm.dd', CONVERT(VARCHAR(100), @Date, 102)
UNION SELECT '103', 'British/French', 'dd/mm/yyyy', CONVERT(VARCHAR(100), @Date, 103)
UNION SELECT '104', 'German', 'dd.mm.yyyy', CONVERT(VARCHAR(100), @Date, 104)
UNION SELECT '105', 'Italian', 'dd-mm-yyyy', CONVERT(VARCHAR(100), @Date, 105)
UNION SELECT '106', '', 'dd mon yyyy', CONVERT(VARCHAR(100), @Date, 106)
UNION SELECT '107', '', 'Mon dd, yyyy', CONVERT(VARCHAR(100), @Date, 107)
UNION SELECT '108', '', 'hh:mi:ss', CONVERT(VARCHAR(100), @Date, 108)
UNION SELECT '109', 'Default + ms', 'mon dd yyyy hh:mi:ss:mmmAM (or PM)', CONVERT(VARCHAR(100), @Date, 109)
UNION SELECT '110', 'USA', 'mm-dd-yyyy', CONVERT(VARCHAR(100), @Date, 110)
UNION SELECT '111', 'Japan', 'yyyy/mm/dd', CONVERT(VARCHAR(100), @Date, 111)
UNION SELECT '112', 'ISO', 'yyyymmdd', CONVERT(VARCHAR(100), @Date, 112)
UNION SELECT '113', 'Europe default + ms', 'dd mon yyyy hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 113)
UNION SELECT '114', '', 'hh:mi:ss:mmm(24h)', CONVERT(VARCHAR(100), @Date, 114)
UNION SELECT '120', 'ODBC canonical', 'yyyy-mm-dd hh:mi:ss(24h)', CONVERT(VARCHAR(100), @Date, 120)
UNION SELECT '121', 'ODBC canonical + ms', 'yyyy-mm-dd hh:mi:ss.mmm(24h)', CONVERT(VARCHAR(100), @Date, 121)
UNION SELECT '126', 'ISO 8601', 'yyyy-mm-ddThh:mi:ss.mmm (no spaces)', CONVERT(VARCHAR(100), @Date, 126)

An example output that I have just run is:

CONVERT style Standard Format Example
100 Default mon dd yyyy hh:miAM (or PM) Jun 16 2015 11:43AM
101 U.S. mm/dd/yyyy 06/16/2015
102 ANSI yyyy.mm.dd 2015.06.16
103 British/French dd/mm/yyyy 16/06/2015
104 German dd.mm.yyyy 16.06.2015
105 Italian dd-mm-yyyy 16-06-2015
106 dd mon yyyy 16 Jun 2015
107 Mon dd, yyyy Jun 16, 2015
108 hh:mi:ss 11:43:59
109 Default + ms mon dd yyyy hh:mi:ss:mmmAM (or PM) Jun 16 2015 11:43:59:457AM
110 USA mm-dd-yyyy 06-16-2015
111 Japan yyyy/mm/dd 2015/06/16
112 ISO yyyymmdd 20150616
113 Europe default + ms dd mon yyyy hh:mi:ss:mmm(24h) 16 Jun 2015 11:43:59:457
114 hh:mi:ss:mmm(24h) 11:43:59:457
120 ODBC canonical yyyy-mm-dd hh:mi:ss(24h) 2015-06-16 11:43:59
121 ODBC canonical + ms yyyy-mm-dd hh:mi:ss.mmm(24h) 2015-06-16 11:43:59.457
126 ISO 8601 yyyy-mm-ddThh:mi:ss.mmm (no spaces) 2015-06-16T11:43:59.457

Leave a Reply

Your e-mail address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.