Cast and convert in SQL

Cast and convert explicitly convert a certain data type expression to another data type. Cast and convert provide similar functions.

Syntax

CAST expressionAS data_type

 

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

Parameter expression

Is any valid Microsoft SQL Server expression. For more information, see.

Data_type

The data types provided by the target system, including and. User-Defined data types cannot be used. For information about available data types, see data types.

Length

, Or an optional parameter of the data type.

Style

Date Format style to convert or data to character data (, or data type); or string format style, to convert, or data into character data (, or data type ).

SQL Server supports the data format in the Arabic style using the Kuwait algorithm.

In the table, the two columns on the left represent the style values that convert or into character data. Add 100 to the style value to obtain the four-digit year (yyyy) of the century ).

 

Without Century digital (yy)

 

Digital Century (YYYY)

 

Standard

 

Input/Output **-0 or 100 (*) Default Value: Mon dd yyyy hh: miam (or PM) 1101 us mm/DD/yyyy2102ansiyy. mm. dd3103 British/French dd/mm/yy4104 German DD. mm. yy5105 Italian dd-mm-yy6106-dd mon yy7107-mon DD, yy8108-hh: mm: ss-9 or 109 (*) Default Value + millisecond mon dd yyyy hh: MI: SS: mmmam (or pm) 10110 us mm-dd-yy11111 Japan YY/MM/dd12112ISOyymmdd-13 or 113 (*) Europe default value + millisecond dd mon yyyy hh: mm: SS: Mmm (24 h) 14114-hh: MI: SS: mmm (24 h)-20 or 120 (*) ODBC specification yyyy-mm-dd hh: mm: ss [. fff]-21 or 121 (*) ODBC specification (in milliseconds) yyyy-mm-dd hh: mm: ss [. fff]-126 (***) ISO8601yyyy-mm-dd thh: mm: SS: Mmm (without spaces)-130 * Kuwait dd mon yyyy hh: MI: SS: mmmAM-131 * Kuwait dd/mm/yy hh: MI: SS: mmmam

* The default value (Style 0, 100, 9, 109, 13, 113, 20, 120, 21, or 121) always returns century digits (yyyy ).

** Input when converted to character data and output when converted to character data.

* ** It is specially used for XML. For the conversion from or to data, the output format is shown in the table. For conversion from, or to data, the output is equivalent to style 2. For conversion from data to data, the output is equivalent to style 1.

(500) {This. resized = true; this. width = 500;} "onmouseover =" this. style. cursor = 'hand' "onclick =" {window. open ('mk: @ msitstore: C: Program % 20 filesmicrosoft % 20sql % 20server80toolsbookstsqlref. CHM:/basics/important.gif ');} "src =" MK: @ msitstore: C: Program % 20 filesmicrosoft % 20sql % 20server80toolsbookstsqlref. CHM:/basics/important.gif "width = 12 Border = 0>ImportantBy default, SQL Server interprets two-digit years based on the end year 2049. That is, the year 49 with two digits is interpreted as 2049, and the year 50 with two digits is interpreted as 1950. Many client applications (such as client applications based on OLE automation objects) Use 2030 as the end year. SQL Server provides a configuration option ("") to change the end year used by SQL Server and process the date in a consistent manner. However, the safest way is to specify a four-digit year.

When converting from data to character data, the style that contains seconds or milliseconds will display zero at these locations. When converting from or to a value, you can use an appropriate or data type length to cut out the date portion that is not needed.

The following table shows the style values when converting from or to character data.

 

Value

 

The maximum output (default) is 6 digits. Use scientific notation as needed. Always 8 bits. Always use scientific notation. Always a 16-bit value. Always use scientific notation.

In the following table, the left column indicates the style value when converting from or to character data.

 

Value

 

The output (default) digits on the left of the decimal point are not separated by commas. The two digits on the right of the decimal point, for example, 4235.98. Each three digits on the left of the decimal point are separated by commas. The two digits on the right of the decimal point, for example, 3,510.92. Each three digits on the left of the decimal point are not separated by commas. The four digits on the right of the decimal point, for example, 4235.9819.

The return type returns the same value as Data Type 0.