Skip Navigation Links
Home
Articles
SQL Server 2012
SQL Server 2014
SQL Server 2016
FAQ
Forums
Practice Test
Bookstore
Tip of the Day : Transact-SQL Set Operators (UNION, EXCEPT and INTERSECT)
Error Messages
Home > SQL Server Error Messages > Msg 281 - <Style number> is not a valid style number when converting from datetime to a character string.
SQL Server Error Messages - Msg 281 - <Style number> is not a valid style number when converting from datetime to a character string.

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: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
Related Articles :