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