SQL Server data type conversions

Both cast and convert in SQL are expressions used to convert an expression of one data type to another data type. CAST and CONVERT provide similar functionality, except that the syntax is different. Convert is generally used in time conversion because it adds a style to the cast and can be converted to a different time format.

First, the syntax:

1. Use CAST

CAST (expression as data_type)

2. Use CONVERT

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

3. Parameter description

Expression

is any valid expression.

Data_type:

The data types provided by the target system, including bigint and sql_variant. You cannot use a user-defined data type.

Length

Optional parameters for nchar, nvarchar, char, varchar, binary, or varbinary data types.

Style

A date format style whereby DateTime or smalldatetime data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data type) or a string format style that will float, Real, money, or smallmoney data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Iii. description of the style parameter of the CONVERT function

1. Date and Time Styles

century Digit (yy) with century digit (yyyy) input/output

-0 ormon dd yyyy hh:miam (or PM)

11011 = mm/dd/yy101 = mm/dd/yyyy

21022 = yy.mm.dd102 = yyyy.mm.dd

31033 = dd/mm/yy103 = dd/mm/yyyy

41044 = dd.mm.yy104 = dd.mm.yyyy

55 = dd-mm-yy= dd-mm-yyyy

61066 = dd Mon yy106 = dd Mon yyyy

71077 = Mon dd, yy107 = Mon dd, yyyy

8108HH:MI:SS

-9 or 109mon dd yyyy hh:mi:ss:mmmAM (or PM)

Tento ten = Mm-dd-yy= mm-dd-yyyy

One111= yy/mm/dd111 = yyyy/mm/dd

yymmdd= YYYYMMDD

-13 or 113dd Mon yyyy hh:mi:ss:mmm (24h)

dd Mon yyyy hh:mi:ss:mmm (24h)

-20 oryyyy-mm-dd hh:mi:ss (24h)

-21 or 121yyyy-mm-dd hh:mi:ss.mmm (24h)

-126yyyy-mm-ddthh:mi:ss.mmm (no spaces)

-127yyyy-mm-ddthh:mi:ss.mmmz (no spaces)

-dd Mon yyyy hh:mi:ss:mmmAM

-131dd/mm/yyyy Hh:mi:ss:mmmAM

2, float and real styles

Value output

0 (default) contains up to 6 bits. Use scientific notation as needed.

1 is always a 8-bit value. Always use scientific notation.

2 is always a 16-bit value. Always use scientific notation.

3. Money and smallmoney style

Value output

0 Each three digits to the left of the decimal point is not separated by commas, and two digits to the right of the decimal point, for example 4235.98.

1 Each three digits to the left of the decimal point is separated by commas, and two digits to the right of the decimal point, such as 3,510.92.

2 each three digits to the left of the decimal point is separated by commas, and two digits to the right of the decimal point, such as 3,510.92.

126 is equivalent to style 2 when converted to char (n) or varchar (n)

SQL Server data type conversions