Date Styles
There are a number of date style codes that can be used as the third parameter of the Convert function. They are as follows:
Style Code | Style | Format | Example |
---|
0 or 100 | Default. Equivalent to not specifying a style code. | mon dd yyyy hh:mmAM | Sep 8 2007 9:00PM |
1 | USA date. | mm/dd/yy | 09/08/07 |
2 | ANSI date. | yy.mm.dd | 07.09.08 |
3 | UK / French date. | dd/mm/yy | 08/09/07 |
4 | German date. | dd.mm.yy | 08.09.07 |
5 | Italian date. | dd-mm-yy | 08-09-07 |
6 | Abbreviated month. | dd mmm yy | 08 Sep 07 |
7 | Abbreviated month. | mmm dd, yy | Sep 08, 07 |
8 or 108 | 24 hour time. | HH:mm:ss | 21:00:00 |
9 or 109 | Default formatting with seconds and milliseconds appended. | mon dd yyyy hh:mm:ss:fffAM | Sep 8 2007 9:00:00:000PM |
10 | USA date with hyphen separators. | mm-dd-yy | 09-08-07 |
11 | Japanese date. | yy/mm/dd | 07/09/08 |
12 | ISO date. | yymmdd | 070908 |
13 or 113 | European default with seconds and milliseconds. | dd mon yyyy HH:mm:ss:fff | 08 Sep 2007 21:00:00:000 |
14 or 114 | 24 hour time with milliseconds. | HH:mm:ss:fff | 21:00:00:000 |
20 or 120 | ODBC canonical date and time. | yyyy-mm-dd HH:mm:ss | 2007-09-08 21:00:00 |
21 or 121 | ODBC canonical date and time with milliseconds. | yyyy-mm-dd HH:mm:ss.fff | 2007-09-08 21:00:00.000 |
101 | USA date with century. | mm/dd/yyyy | 09/08/2007 |
102 | ANSI date with century. | yyyy.mm.dd | 2007/09/08 |
103 | UK / French date with century. | dd/mm/yyyy | 08/09/2007 |
104 | German date with century. | dd.mm.yyyy | 08.09.2007 |
105 | Italian date with century. | dd-mm-yyyy | 08-09-2007 |
106 | Abbreviated month with century. | dd mmm yyyy | 08 Sep 2007 |
107 | Abbreviated month with century. | mmm dd, yyyy | Sep 08, 2007 |
110 | USA date with hyphen separators and century. | mm-dd-yyyy | 09-08-2007 |
111 | Japanese date with century. | yyyy/mm/dd | 2007/09/08 |
112 | ISO date with century. | yymmdd | 20070908 |
126 | ISO8601, for use in XML. | yyy-mm-ddThh:mm:ss | 2007-09-08T21:00:00 |
Float / Real Styles
When converting a floating-point value held in a Float or Real data type, three formats are available. The default format (0) returns a string containing a maximum of six significant digits. If required, scientific notation is applied. The other two formats always return a string containing scientific notation and either eight or sixteen digits for the mantissa:
DECLARE @From FLOAT
SET @From = 1.23456789
PRINT convert(VARCHAR, @From, 0) -- 1.23457
PRINT convert(VARCHAR, @From, 1) -- 1.2345679e+000
PRINT convert(VARCHAR, @From, 2) -- 1.234567890000000e+000
Money Styles
Three styles are available when converting Money and SmallMoney data to text. These control whether commas are used to separate digit groups and how many decimal places should be included.
Style Code | Style | Example |
---|
0 | Default. No commas and two decimal places. | 1234567.89 |
1 | Commas every three digits of the integer part and two decimal places. | 1,234,567.89 |
2 | No commas and four decimal places. | 1234567.8901 |
11 October 2009