|
|
Error Message
Server: Msg 281, Level 16, State 1, Line 2
<Style number> is not a valid style number when
converting from datetime to a character string.
Causes
The CONVERT function (as well as the CAST function) explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality but differs in the syntax. The syntax for the CAST function is as follows:
|
CAST( <expression> AS <data_type [ ( length ) ] > )
On the other hand, the syntax for the CONVERT function is as follows:
CONVERT( <data_type [ ( length ) ] >, <expression> [, <style> ] )
As can be seen from the syntax of the CONVERT function, it has a third optional parameter for the style, which is the style of the date format used to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or the string format used to convert float, real, money or smallmoney data to character data type.
The following are the valid values for the style parameter when converting datetime or smalldatetime data to character data:
| - |
0 or 100 |
Default |
Mon dd yyyy hh:mmAM (or PM) |
| 1 |
101 |
U.S. |
mm/dd/yyyy |
| 2 |
102 |
ANSI |
yy.mm.dd |
| 3 |
103 |
British / French |
dd/mm/yy |
| 4 |
104 |
German |
dd.mm.yy |
| 5 |
105 |
Italian |
dd-mm-yy |
| 6 |
106 |
- |
dd mon yy |
| 7 |
107 |
- |
Mon dd, yy |
| 8 |
108 |
- |
hh:mm:ss |
| - |
9 or 109 |
Default + milliseconds |
Mon dd yyyy hh:mi:ss:mmmAM (or PM) |
| 10 |
110 |
USA |
mm-dd-yy |
| 11 |
111 |
Japan |
yy/mm/dd |
| 12 |
112 |
ISO |
yymmdd |
| - |
13 or 113 |
Europe default + milliseconds |
dd mon yyyy hh:mm:ss:mmm(24h) |
| 14 |
114 |
- |
hh:mi:ss:mmm(24h) |
| - |
20 or 120 |
ODBC canonical |
yyyy-mm-dd hh:mi:ss(24h) |
| - |
21 or 121 |
ODBC canonical (with milliseconds) |
yyyy-mm-dd hh:mi:ss.mmm(24h) |
| - |
126 |
ISO8601 |
yyyy-mm-ddThhmmLss.mmm (no spaces) |
| - |
127 |
ISO8601 with time zone Z |
yyyy-mm-ddThhmm:ss.mmmZ (no spaces) |
| - |
130 |
Hijri |
dd mon yyyy hh:mi:ss:mmmAM |
| - |
131 |
Hijri |
dd/mm/yy hh:mi:ss:mmmAM |
Specifying any other value in the style parameter not included in this list when converting datetime or smalldatetime to character data will generate this error. Here’s an example of how this error can be encountered:
SELECT CONVERT(VARCHAR(10), GETDATE(), 115)
Msg 281, Level 16, State 1, Line 1
115 is not a valid style number when converting from datetime to a character string.
Solution / Work Around:
To avoid from encountering this error, make sure that the style passed on the third parameter of the CONVERT function is one of the valid values as shown in the table above. Here’s a script that will generate the list of valid values for the style parameter when converting a datetime or smalldatetime data to a character data with the corresponding result:
DECLARE @Style INT
DECLARE @ValidStyles TABLE (
[Style] INT,
[Output] NVARCHAR(50)
)
SET @Style = 0
WHILE @Style < 200
BEGIN
BEGIN TRY
INSERT INTO @ValidStyles ( [Style], [Output] )
VALUES ( @Style, CONVERT(NVARCHAR(50), GETDATE(), @Style ) )
END TRY
BEGIN CATCH
END CATCH
SET @Style = @Style + 1
END
SELECT * FROM @ValidStyles
Here's a sample output of this script:
Style Output
----------- --------------------------------------------------
0 Oct 16 2012 10:58PM
1 10/16/12
2 12.10.16
3 16/10/12
4 16.10.12
5 16-10-12
6 16 Oct 12
7 Oct 16, 12
8 22:58:03
9 Oct 16 2012 10:58:03:323PM
10 10-16-12
11 12/10/16
12 121016
13 16 Oct 2012 22:58:03:323
14 22:58:03:323
20 2012-10-16 22:58:03
21 2012-10-16 22:58:03.323
22 10/16/12 10:58:03 PM
23 2012-10-16
24 22:58:03
25 2012-10-16 22:58:03.323
100 Oct 16 2012 10:58PM
101 10/16/2012
102 2012.10.16
103 16/10/2012
104 16.10.2012
105 16-10-2012
106 16 Oct 2012
107 Oct 16, 2012
108 22:58:03
109 Oct 16 2012 10:58:03:333PM
110 10-16-2012
111 2012/10/16
112 20121016
113 16 Oct 2012 22:58:03:333
114 22:58:03:333
120 2012-10-16 22:58:03
121 2012-10-16 22:58:03.333
126 2012-10-16T22:58:03.333
127 2012-10-16T22:58:03.333
130 1 ذو الحجة 1433 10:58:03:333PM
131 1/12/1433 10:58:03:333PM
|