Usage and difference between cast and convert in SQL

1. Grammar:

1. Use CAST

CAST (expression AS data_type)

2. Use CONVERT

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

3. Parameters Description

　expression

　　is any valid expression.

　data_type:

　The data type provided by the target system, including bigint and sql_variant. User-defined data types cannot be used.

　length

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

　style

　A date formatting style, which converts datetime or smalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types); or a string formatting style, which converts float, real, money, or smallmoney Data is converted to character data (nchar, nvarchar, char, varchar, nchar, or nvarchar data types).

2. Code examples of cast and convert

select 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(20), @Num, 0)  -- 1234.56
 
select CONVERT(varchar(20), @Num, 1)  -- 1,234.56
 
select CONVERT(varchar(20), @Num, 2)  -- 1234.5600

3. Style parameter description of convert function

1. Date and Time styles

2. Float and real styles

3. money and smallmoney style