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