How to realize data type conversion in SqlServer

SqlServer in how to achieve data type conversion, many novices are not very clear about this, in order to help you solve this problem, the following editor will explain in detail for you, people with this need can come to learn, I hope you can gain something.

Both cast and convert in SQL are used to convert expressions of one data type to another. CAST and CONVERT provide similar functionality, but with different syntax. Convert is generally used in time conversion, because it adds an extra style than cast, which can be converted into different time formats.

First, grammar:

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. 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 to convert datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types), or string format styles to convert float, real, money, or smallmoney data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

Third, the style parameter description of convert function.

1. Date and Time styl

No Century Digital (yy) with Century Digital (yyyy) input / output

-0 or 100mon 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

51055 = dd-mm-yy105 = 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)

1011010 = mm-dd-yy110 = mm-dd-yyyy

1111111 = yy/mm/dd111 = yyyy/mm/dd

1211212 = yymmdd112 = yyyymmdd

-13 or 113dd mon yyyy hh:mi:ss:mmm (24 hours)

14114dd mon yyyy hh:mi:ss:mmm (24 hours)

-20 or 120yyyy-mm-dd hh:mi:ss (24 hours)

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

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

-127yyyy-mm-ddThh:mi:ss.mmmZ (no spaces)

-130dd mon yyyy hh:mi:ss:mmmAM

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

2. Float and real styl

Value output

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

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

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

3. Money and smallmoney styl

Value output

Every three digits to the left of the 0 decimal point are not separated by a comma, and two digits are taken to the right of the decimal point, for example, 4235.98.

1 comma separates every three digits on the left side of the decimal point, and takes two digits to the right of the decimal point, such as 3510.92.

2 comma separates every three digits on the left side of the decimal point, and take two digits to the right of the decimal point, for example, 3510.92.

When converted to char (n) or varchar (n), it is equivalent to style 2

Is it helpful for you to read the above content? If you want to know more about the relevant knowledge or read more related articles, please follow the industry information channel, thank you for your support.