Home > php教程 > PHP开发 > body text

Usage and difference between cast and convert in SQL

高洛峰
Release: 2016-12-14 13:23:40
Original
2867 people have browsed it

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
Copy after login

3. Style parameter description of convert function

1. Date and Time styles

Usage and difference between cast and convert in SQL

2. Float and real styles

Usage and difference between cast and convert in SQL

3. money and smallmoney style

Usage and difference between cast and convert in SQL

Related labels:
source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Recommendations
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template