CAST and CONVERT (SQL Server Compact)

Explicitly converts an expression of one data type to another. CAST is a syntactic variant of CONVERT.

Syntax

Syntax for CAST:
CAST ( expression AS data_type [ (length ) ])
Syntax for CONVERT:
CONVERT ( data_type [ ( length ) ] ,expression [ ,style ] )

Arguments

  • data_type
    The target system-supplied data type is bigint. 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 expression. For more information, see Expressions (SQL Server Compact).

  • Style
    The style of date format that is 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 Compact 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-digit year that includes the century (yyyy).

Without century (yy)

With century (yyyy)

Standard

Input/Output**

-

0 or 100 (*)

Default

mon dd yyyy hh:mi AM (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:mmm AM (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:mmm AM

-

131*

Hijri****

dd/mm/yy hh:mi:ss:mmm AM

* 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 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.

Important

When using CAST or CONVERT for nchar, nvarchar, binary, and varbinary, SQL Server truncates values to maximum of 30 characters. SQL Server Compact allows 4000 for nchar and nvarchar, and 8000 for binary and varbinary. Due to this, results generated by querying SQL Server and SQL Server Compact are different. In cases where the size of the data types is specified such as nchar(200), nvarchar(200), binary(400), varbinary(400), the results are consistent across SQL Server and SQL Server Compact.

The following illustration shows explicit and implicit data conversions in SQL Server Compact.

Explicit and implicit data conversions

Examples

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

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

Cast converts an expression of one data type to another. In the following example, the result of the conversion is 10.

SELECT CAST(10.6496 AS int)

When a data conversion occurs in which the target data type has fewer decimal places than the source data type, the value is rounded. For example, the result of the following conversion is $10.3497.

SELECT CAST(10.3496847 AS money)