Export (0) Print
Expand All
Expand Minimize

CONVERT (SQL Server Compact Edition)

Explicitly converts an expression of one data type to another.


CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] )

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 style is NULL, the result returned is also NULL.

For more information, see the "Remarks" section later in this topic.

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.

Explicit and implicit data conversions

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

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

Community Additions

ADD
Show:
© 2014 Microsoft