//字符转日期
CONVERT(datetime, '20000704')
CREATE TABLE my_date (Col1 datetime) GO
INSERT INTO my_date VALUES (CONVERT(char(10), GETDATE(), 112)) GO
drop table my_date; GO
----------------------------------------------------------- 5.1.4 ISNULL()
功能:检查check_expression是空值，就用replacement_value替代
语法:ISNULL(check_expression, replacement_value) 代码:
SELECT BillingDate,
ISNULL(BillingDate, '1900-01-01') AS NewDate FROM Billings
----------------------------------------------------------- 5.1.5 NULLIF()
功能:两个表达式相等，返回null,否则返回第1个表达式 语法:ISNULL(expression1, expression2) 代码:
DECLARE @Value1 int DECLARE @Value2 int SET @Value1 = 55 SET @Value2 = 955
SELECT NULLIF(@Value1, @Value2) GO 输出 55
DECLARE @Value1 int DECLARE @Value2 int SET @Value1 = 55 SET @Value2 = 55
SELECT NULLIF(@Value1, @Value2) GO 输出 NULL
----------------------------------------------------------- 5.2聚集函数Aggregate Functions
语法:select AggregateFunctions(column-name) sum(column-name):计算字段总和 avg(column-name):计算字段平均值 min(column-name):计算字段最小值 max(column-name):计算字段最大值 count(column-name):计算字段非空值的个数 count(*):计算查询结果的记录个数 代码: //use pubs
select sum(qty) as sum_qty, avg(qty) as avg_qty, min(qty) as min_qty, max(qty) as max_qty, count(qty) as count_qty, count(*) as total_qty from sales
-----------------------------------------------------------
5.3字符函数char Functions 1. ASCII()
//函数返回字符表达式最左端字符的ASCII 码值 2. Char()
//函数用于将ASCII 码转换为字符--如果没有输入0 ~ 255 之间的ASCII 码值CHAR 函数会返回一个NULL 3. CHARINDEX()
//函数返回字符串中某个指定的子串出现的开始位置 4. DIFFERENCE() 5. FORMATMESSAGE() 6. LEFT() 7. LEN() 8. LOWER()
//函数把字符串全部转换为小写 9. LTRIM()
//函数把字符串头部的空格去掉 10.nchar() 11.PATINDEX() 12.QUOTENAME() 13.REPLACE()
//函数返回被替换了指定子串的字符串 14.REPLICATE()
/函数返回一个重复指定次数的字符串 15.REVERSE()
//函数将指定的字符串的字符排列顺序颠倒 16.Right() 17.RTRIM()
/函数把字符串尾部的空格去掉 18.SOUNDEX() 19.SPACE()
//函数返回一个有指定长度的空白字符串 20.STR()
//函数把数值型数据转换为字符型数据 21.STUFF()
//函数用另一子串替换字符串指定位置长度的子串 22.SUBSTRING() //函数返回子字符串 23.UNICODE() 24.UPPER()
//函数把字符串全部转换为大写
-----------------------------------------------------------
5.4日期函数Date Functions