cast和convert用于数据类型转换，round用于四舍五入取近似字，numeric用于精确设置数字精度(长度)和小数位数。

T-SQL:

CAST and CONVERT

Explicitly converts an expression of one data type to another. CAST and CONVERT provide similar functionality.

Syntax

Using CAST:

CAST ( expression AS data_type)

Using CONVERT:

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

Arguments

expression

Is any valid Microsoft® SQL Server™ expression. For more information, seeExpressions.

data_type

Is the target system-supplied data type, including bigint andsql_variant. User-defined data types cannot be used. For more information about available data types, seeData Types.

length

Is an optional parameter of nchar, nvarchar,char, varchar, binary, or varbinary data types.

style

Is the style of date format used to convert datetime orsmalldatetime data to character data (nchar, nvarchar, char, varchar, nchar, ornvarchar data types), or the string format when converting float, real, money, or smallmoney data to character data (nchar,nvarchar, char, varchar, nchar, or nvarchar data types).

ROUND(nExpression, nDecimalPlaces)

SET DECIMALS TO 4
SET FIXED ON     && Fix decimal display
CLEAR

? ROUND(1234.1962, 3) && Displays 1234.1960
? ROUND(1234.1962, 2) && Displays 1234.2000
? ROUND(1234.1962, 0) && Displays 1234.0000
? ROUND(1234.1962, -1)  && Displays 1230.0000
? ROUND(1234.1962, -2)  && Displays 1200.0000
? ROUND(1234.1962, -3)  && Displays 1000.0000

numeric(p,s)

• p

  Specifies the precision, or the number of digits the object can hold.

• s

  Specifies the scale, or the number of digits that can be placed to the right of the decimal point.

  p and s must observe the rule: 0 <= s <=p <= 38.

The default maximum precision of numeric and decimal data types is 38. In Transact-SQL,numeric is functionally equivalent to the decimal data type.

Use the decimal data type to store numbers with decimals when the data values must be stored exactly as specified.

将numeric转换为float或real会导致精度的降低。从 int、smallint、tinyint、float、real、money或smallmoney转换为decimal或numeric 会导致溢出。将数字转换为较低精度和小数位数的numeric值时，SQL Server会进行舍入。但如果SET ARITHABORT选项为ON，则发生溢出时，SQL Server会产生错误。 若仅降低精度和小数位数，则不会产生错误。  
在将float值或实数值转换为numeric类型时，numeric值不会超过 17 位小数。 任何小于5E-18的float 值总是会转换为0。

eg:

select cast(round(88.123456789,2) as numeric(5,3)) as TEST

select cast(round(88.123456789,4) as numeric(5,3)) as TEST

select cast(round(88.123456789,4) as numeric(5,4)) as TEST

select cast(round(88.123456789,4) as numeric(8,6)) as TEST

最后

以上就是辛勤黑米为你收集整理的SQL数字类型转换和四舍五入cast和convert用于数据类型转换，round用于四舍五入取近似字，numeric用于精确设置数字精度(长度)和小数位数。T-SQL:CAST and CONVERTROUND( ) Function的全部内容，希望文章能够帮你解决SQL数字类型转换和四舍五入cast和convert用于数据类型转换，round用于四舍五入取近似字，numeric用于精确设置数字精度(长度)和小数位数。T-SQL:CAST and CONVERTROUND( ) Function所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。