Skip to content

SQL Server Error Messages - Msg 281

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:

Without century (yy) With century (yyyy) Standard Input / Output
- 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🇲🇲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🇲🇲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