SQL Server 2000 functions use-CAST and CONVERT)

Explicitly converts a data type expression to another data type. CAST and CONVERT provide similar functions.

Syntax

Use CAST:

CAST (expression AS data_type)

Use CONVERT:

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

Parameters

Expression

Is any valid Microsoft SQL Server expression.

Data_type

The data types provided by the target system, including bigint and SQL _variant. User-Defined data types cannot be used

Length

Optional parameters of the nchar, nvarchar, char, varchar, binary, or varbinary data type.

Style

Date Format style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type); or string format style, to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar 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 converting datetime or smalldatetime to the style value of the 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)
1	101	USA	Mm/dd/yyyy
2	102	ANSI	Yy. mm. dd
3	103	UK/France	Dd/mm/yy
4	104	Germany	Dd. mm. yy
5	105	Italy	Dd-mm-yy
6	106	-	Dd mon yy
7	107	-	Mon dd, yy
8	108	-	Hh: mm: ss
-	9 or 109 (*)	Default Value + millisecond	Mon dd yyyy hh: mi: ss: mmmAM (or PM)
10	110	USA	Mm-dd-yy
11	111	Japan	Yy/mm/dd
12	112	ISO	Yymmdd
-	13 or 113 (*)	European default value + millisecond	Dd mon yyyy hh: mm: ss: mmm (24 h)
14	114	-	Hh: mi: ss: mmm (24 h)
-	20 or 120 (*)	ODBC specifications	Yyyy-mm-dd hh: mm: ss [. fff]
-	21 or 121 (*)	ODBC specifications (in milliseconds)	Yyyy-mm-dd hh: mm: ss [. fff]
-	126 (***)	ISO8601	Yyyy-mm-dd Thh: mm: ss: mmm (excluding 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 datetime; output when converted to character data.
* ** It is specially used for XML. For the conversion from datetime or smalldatetime to character data, the output format is shown in the table. For data conversion from float, money, smallmoney to character, the output is equivalent to style 2. For data conversion from real to character, the output is equivalent to style 1.
Example: CONVERT (char (10), s. Season_PlanDate, 102) AS scheduled date
Example:
1. Select Convert (Varchar (10), Cast ('000000' As DateTime), 20070723)
2. Use Convert or Cast for forced conversion.
Syntax:
Convert (type to be converted, column or expression to be converted)
Cast (the type of the converted column or expression AS to be converted)