Convert and cast differences in SQL

The use and difference of cast and convert in SQL more the/1/ -Source: SQL Learning Tour:14125Learning Tags:cast ConvertSQL This article guide: both cast and convert in SQL are expressions used to convert an expression of one data type to another data type. CASTAndCONVERTprovides 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:1UseCAST 　　 CAST(Expression asdata_type)2UseCONVERT 　　 CONVERT(data_type[(length)], expression[, Style])3, parameter description expression is any valid expression. Data_type: The type of data provided by the target system, includingbigintand sql_variant.　　You cannot use a user-defined data type. Lengthnchar、nvarchar、Char、varchar、binaryOrvarbinaryOptional parameters for the data type. Style date format, which willdatetimeOrsmalldatetimeData is converted to character data (nchar、nvarchar、Char、varchar、ncharOrnvarcharData type), or a string format style that willfloat、Real、 MoneyOrsmallmoneyData is converted to character data (nchar、nvarchar、Char、varchar、ncharOrnvarchardata type). Ii. code samples for cast and convert SQL code replicationSelect CAST('123'  as int)--123 Select CONVERT(int,'123')--123   Select CAST(123.4  as int)--123 Select CONVERT(int,123.4)--123   Select CAST('123.4'  as int) Select CONVERT(int,'123.4') --Conversion failed when converting the varchar value ' 123.4 ' to data type int.   Select CAST('123.4'  as decimal)--123 Select CONVERT(decimal,'123.4')--123   Select CAST('123.4'  as decimal(9,2))--123.40 Select CONVERT(decimal(9,2),'123.4')--123.40   Declare @Num  Money Set @Num = 1234.56 Select CONVERT(varchar( -),@Num,0)--1234.56 Select CONVERT(varchar( -),@Num,1)--1,234.56 Select CONVERT(varchar( -),@Num,2)--1234.5600

DECLARE @i float
Set @i=11134.13409890890765656
Select CONVERT (varchar (+), @i,0)
, CONVERT (varchar (+), @i,1)
, CONVERT (varchar (+), @i,2)
---------------------------------------- ---------------------------------------- ------------------------------- ---------
11134.1 1.1134134e+004 1.113413409890891e+004

Iii. description of the style parameter of the CONVERT function

1. Date and Time Styles

Without century digits (yy)	With century digits (yyyy)	Input/Output
-	0 or	Mon dd yyyy hh:miam (or PM)
1	101	1 = mm/dd/yy 101 = mm/dd/yyyy
2	102	2 = yy.mm.dd 102 = Yyyy.mm.dd
3	103	3 = Dd/mm/yy 103 = dd/mm/yyyy
4	104	4 = dd.mm.yy 104 = dd.mm.yyyy
5	105	5 = Dd-mm-yy DD-MM-YYYY =
6	106	6 = dd Mon yy 106 = DD Mon yyyy
7	107	7 = Mon dd, yy 107 = Mon DD, yyyy
8	108	Hh:mi:ss
-	9 or 109	Mon dd yyyy hh:mi:ss:mmmAM (or PM)
10	110	Ten = Mm-dd-yy MM-DD-YYYY =
11	111	one = Yy/mm/dd 111 = Yyyy/mm/dd
12	112	YYMMDD = YYYYMMDD =
-	or 113	DD Mon yyyy hh:mi:ss:mmm (24h)
14	114	DD Mon yyyy hh:mi:ss:mmm (24h)
-	or	Yyyy-mm-dd Hh:mi:ss (24h)
-	or 121	Yyyy-mm-dd hh:mi:ss.mmm (24h)
-	126	Yyyy-mm-ddthh:mi:ss.mmm (no spaces)
-	127	Yyyy-mm-ddthh:mi:ss.mmmz (no spaces)
-	130	DD Mon yyyy hh:mi:ss:mmmAM
-	131	DD/MM/YYYY Hh:mi:ss:mmmAM

2, float and real styles

Value	Output
0(default value)	Contains up to 6 bits. use scientific notation as needed.
1	Always a 8-bit value. always use scientific notation.
2	Always a 16-bit value. always use scientific notation.

3. Money and smallmoney style

Value	Output
0	Every three digits to the left of the decimal point are not separated by commas, and two digits to the right of the decimal point, for example 4235.98.
1	A comma separates each three digits to the left of the decimal point and two digits to the right of the decimal point, such as 3,510.92.
2	A comma separates each three digits to the left of the decimal point and two digits to the right of the decimal point, such as 3,510.92.
126	When converted to char (n) or varchar (n), it is equivalent to style 2

Convert and cast differences in SQL