CONVERT (SQL Server Compact Edition)
Explicitly converts an expression of one data type to another.
Syntax
CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] )
Arguments
- data_type
The target system-supplied data type, including bigint and sql_variant. User-defined data types cannot be used. For more information about available data types, see Data Types.
- length
An optional parameter of nchar, nvarchar, char, varchar, binary, or varbinary data types.
- expression
Any valid SQL Server Compact Edition expression. For more information, see Expressions (SQL Server Compact Edition).
style
The style of 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 when you convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types). If styleis NULL, the result returned is also NULL.For more information, see the "Remarks" section later in this topic.
Remarks
SQL Server 2005 Compact Edition (SQL Server Compact Edition) supports the date format in Arabic style, using the Kuwaiti algorithm.
In the following table, the two columns on the left represent the style values for datetime or smalldatetime conversion to character data. Add 100 to a style value to obtain a four-place year that includes the century (yyyy).
Without century (yy) | With century (yyyy) | Standard | Input/Output** |
---|---|---|---|
- |
0 or 100 (*) |
Default |
mon dd yyyy hh:miAM (or PM) |
1 |
101 |
United States |
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 |
United States |
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-ddThh:mm:ss.mmm(no spaces) |
- |
130* |
Hijri**** |
dd mon yyyy hh:mi:ss:mmmAM |
- |
131* |
Hijri**** |
dd/mm/yy hh:mi:ss:mmmAM |
* 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).
** Input when you convert to datetime. Output when you convert to character data.
*** 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.
**** Hijri is a calendar system with several variations, of which SQL Server Compact Edition uses the Kuwaiti algorithm.
The following table shows the style values for float or real conversion to character data.
Value | Output |
---|---|
0 (default) |
Six digits maximum. Use in scientific notation, when appropriate. |
1 |
Always eight 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 or smallmoney conversion to character data.
Value | Output |
---|---|
0 (default) |
No commas the left of the decimal point, and two digits to the right of the decimal point. Example: 4235.98. |
1 |
Commas every three digits to the left of the decimal point, and two digits to the right of the decimal point. Example: 3,510.92. |
2 |
No commas to the left of the decimal point, and four digits to the right of the decimal point. Example: 4235.9819. |
Use style 128 to remove trailing zeros from a result set when you convert from numeric, or decimal data to character data.
The following graphic shows explicit and implicit data conversions in SQL Server Compact Edition.
Example
The following example converts a datetime value in to an nvarchar value.
SELECT OrderDate, CONVERT(nvarchar(10), OrderDate, 101)
FROM Orders