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.

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 email address will not be published. Required fields are marked *

CJayMead's SQL Server, Endeca, MS Office Help © 2016