SQLServer中 ROUND函数、CAST 和 CONVERT函数 的使用详解

一、ROUND函数

返回数字表达式并四舍五入为指定的长度或精度。

1、语法

ROUND ( numeric_e-xpression , length [ , function ] )

参数	描述
numeric_e-xpression	必需。精确数字或近似数字数据类型类别的表达式（bit 数据类型除外）。
length	必需。将要四舍五入的精度。length 必须是 tinyint、smallint 或int。当 length 为正数时，numeric_e-xpression 四舍五入为 length 所指定的小数位数。当 length 为负数时，numeric_e-xpression 则按 length 所指定的在小数点的左边四舍五入。
function	选填。要执行的操作类型。function 必须是 tinyint、smallint 或 int。如果省略 function 或 function 的值为 0（默认），numeric_e-xpression 将四舍五入。当指定 0 以外的值时，numeric_e-xpression 将截断 。

 2、案例

------当第三个参数没有设置的时候，默认为0，也就是四舍五入-------
SELECT ROUND(925.678, 2)
-->925.680  结果并非只保留两位小数，而是保留两位有效的小数
SELECT ROUND(925.678, 0)
-->926.000  当精度为0，取整数
SELECT ROUND(925.678, -2)
-->900.000  当精度为负数时，根据小数点左边位数进行四舍五入
SELECT ROUND(925.678, -4)
-->0.000  当精度是负数且大于小数点前的数字个数，ROUND 将返回 0。
SELECT ROUND(925.678, -3)
-->当精度是负数且等于小数点前的数字个数时，报错：将 expression 转换为数据类型 numeric 时出现算术溢出错误！
-->原因是925.678这个常量的数据类型是decimal(6,3)，而结果是1000.000，这便报错了。
-->解决方法也简单，转换数据类型即可。如下:
SELECT ROUND(CAST(925.678 AS decimal(7,3)), -3)


------当第三个参数设置为非0的数，直接截断-------
SELECT ROUND(925.678,1,0)
-->925.700  
SELECT ROUND(925.678,1,2)
-->925.600

SELECT ROUND(925.678,-1,0)
-->930.000
SELECT ROUND(925.678,-1,2)
-->920.000

二、CAST 函数和CONVERT函数

    CAST函数和CONVERT函数功能类似，就语法有点差别，而且默认都是四舍五入的。CAST函数用的比较多，CONVERT函数的优点就是可以格式化日期和数值，待会细说。

1、语法

（1）CAST (expression AS data_type)

参数	描述
expression	必需。任何有效的SQServer表达式。
AS	必需。用于分隔两个参数，在AS之前的是要处理的数据，在AS之后是要转换的数据类型。
data_type	必需。目标系统所提供的数据类型，包括bigint和sql_variant，不能使用用户定义的数据类型。如果转换时没有指定数据类型的长度，则SQServer自动提供长度为30。

（2）CONVERT(data_type(length),data_to_be_converted,style)

参数	描述
data_type(length)	必需。data_type(length) 规定目标数据类型（带有可选的长度）。
data_to_be_converted	必需。data_to_be_converted 含有需要转换的值。
style	选填。style 规定日期/时间的输出格式。默认0或100。

当格式化日期时，style可选填的值如下：

Style ID	Style 格式
100 或者 0	mon dd yyyy hh:miAM （或者 PM）
101	mm/dd/yy
102	yy.mm.dd
103	dd/mm/yy
104	dd.mm.yy
105	dd-mm-yy
106	dd mon yy
107	Mon dd, yy
108	hh:mm:ss
109 或者 9	mon dd yyyy hh:mi:ss:mmmAM（或者 PM）
110	mm-dd-yy
111	yy/mm/dd
112	yymmdd
113 或者 13	dd mon yyyy hh:mm:ss:mmm(24h)
114	hh:mi:ss:mmm(24h)
120 或者 20	yyyy-mm-dd hh:mi:ss(24h)
121 或者 21	yyyy-mm-dd hh:mi:ss.mmm(24h)
126	yyyy-mm-ddThh:mm:ss.mmm（没有空格）
130	dd mon yyyy hh:mi:ss:mmmAM
131	dd/mm/yy hh:mi:ss:mmmAM

2、案例

 --> 截取字符串
SELECT CAST('12222asdfds' as varchar(8)); -->12222asd
SELECT CONVERT(varchar(8), '12222asdfds'); -->12222asd
 --> 先将数字 1 转换成 字符串，两个字符串间进行相加
SELECT 'ABC' + CAST(1 AS varchar(3));  -->ABC1 

 --> 字符串转INT类型
SELECT CAST('123' as int)  -->123
SELECT CONVERT(int, '123')  -->123
 --> decimal/float转INT类型
SELECT CAST(123.5 as int)   -->123
SELECT CONVERT(int, 123.5)  -->123 
 --> 只能强转相近的数值类型，所以报错：在将 varchar 值 '123.567' 转换成数据类型 int 时失败。
SELECT CAST('123.567' as int)
SELECT CONVERT(int, '123.567')

 --> 不能转成INT，但带小数位的字符串，可以定义decimal类型
SELECT CAST('123.567' as decimal(9,2))  -- 123.50
SELECT CONVERT(decimal(9,2), '123.567') -- 123.50
 --> decimal类型，如果不定义精度和小数位，默认为精度为18，小数位为0。
SELECT CAST('123.5' as decimal)  -- 124
SELECT CONVERT(decimal, '123.5') -- 124 

 --> 字符串转换成时间
SELECT CAST('2018-06-02' AS datetime);  
SELECT CONVERT(datetime, '2018-06-02');

  ---------------CONVERT函数----------------

 --> 格式化日期
SELECT CONVERT(VARCHAR,GETDATE())
SELECT CONVERT(VARCHAR,GETDATE(),110) 
SELECT CONVERT(VARCHAR,GETDATE(),106)
SELECT CONVERT(VARCHAR,GETDATE(),113)
 --> 格式化数值
DECLARE @Num money
SET @Num = 7856781234.5678
SELECT CONVERT(varchar(20), @Num, 0)  -- 7856781234.57
SELECT CONVERT(varchar(20), @Num, 1)  -- 7,856,781,234.57
SELECT CONVERT(varchar(20), @Num, 2)  -- 7856781234.5678