Export (0) Print
Expand All

CONVERT

SQL Server 2000

Explicitly converts an expression of one data type to another.

Syntax
CONVERT ( data_type [ ( length ) ] , expression [ , style ] )
Arguments
data_type
Is the target system-supplied data type, including bigint.
length
Is an optional parameter of nchar, nvarchar, binary, or varbinary data types.
expression
Is any valid expression in Microsoft® SQL Server™ 2000 Windows® CE Edition (SQL Server CE).
style
Is the style of date format used to convert datetime data to character data (nchar, nvarchar, nchar, or nvarchar data types), or the string format when converting float, real, or money data to character data (nchar, nvarchar, nchar, or nvarchar data types).

SQL Server CE supports the date format in Arabic style, using the Kuwaiti algorithm.

In the table, the two columns on the left represent the style values for datetime conversion to character data. Add 100 to a style value to get a four-place year that includes the century (yyyy).

Without century (yy) With century (yyyy)
Standard

Input/Output2
- 0 or 100 1 Default mon dd yyyy hh:miAM (or PM)
1 101 USA mm/dd/yy
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 1 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 1 Europe default + milliseconds dd mon yyyy hh:mm:ss:mmm(24h)
14 114 - hh:mi:ss:mmm(24h)
- 20 or 120 1 ODBC canonical yyyy-mm-dd hh:mi:ss(24h)
- 21 or 121 1 ODBC canonical (with milliseconds) yyyy-mm-dd hh:mi:ss.mmm(24h)
- 1263 ISO8601 yyyy-mm-dd Thh:mm:ss.mmm(no spaces)
- 1301 Hijri4 dd mon yyyy hh:mi:ss:mmmAM
- 1311 Hijri4 dd/mm/yy hh:mi:ss:mmmAM

1 The default values (style 0 or 100, 9 or 109, 13 or 113, 20 or 120, and 21 or 121) always return the century (yyyy).
2 Input when converting to datetime; output when converting to character data.
3 Designed for XML use. For conversion from datetime or smalldatetime to character data, the output format is as described in the table. For conversion from float, money, or smallmoney to character data, the output is equivalent to style 2. For conversion from real to character data, the output is equivalent to style 1.
4 Hijri is a calendar system with several variations, of which Microsoft SQL Server 2000 uses the Kuwaiti algorithm.

The following table shows the style values for float or real conversion to character data.

Value Output
0 (default) Maximum of 6 digits. Use in scientific notation, when appropriate.
1 Always 8 digits. Always use in scientific notation.
2 Always 16 digits. Always use in scientific notation.

In the following table, the column on the left represents the style value for money conversion to character data.

Value Output
0 (default) No commas every 3 digits to the left of the decimal point, and 2 digits to the right of the decimal point (for example, 4235.98).
1 Commas every 3 digits to the left of the decimal point, and 2 digits to the right of the decimal point (for example, 3,510.92).
2 No commas every 3 digits to the left of the decimal point, and 4 digits to the right of the decimal point (for example, 4235.9819).
Return Types

Returns the same value as the data_type argument.

Remarks

Data Type Conversion Table

Examples

The following example converts a datetime value in the Northwind database to an nvarchar value.

SELECT OrderDate, CONVERT(nvarchar(10), OrderDate, 101)
FROM Orders

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft