1、通常用到的字符串转日期格式
Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06
Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16
Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06
Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06
Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06
Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06
Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06
Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46
Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM
Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06
Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16
Select CONVERT(varchar(100), GETDATE(), 12): 060516
Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937
Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967
Select CONVERT(varchar(100), GETDATE(), 20): 2006-05-16 10:57:47
Select CONVERT(varchar(100), GETDATE(), 21): 2006-05-16 10:57:47.157
Select CONVERT(varchar(100), GETDATE(), 22): 05/16/06 10:57:47 AM
Select CONVERT(varchar(100), GETDATE(), 23): 2006-05-16
Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47
Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250
Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AM
Select CONVERT(varchar(100), GETDATE(), 101): 05/16/2006
Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16
Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006
Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006
Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006
Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006
Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006
Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49
Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AM
Select CONVERT(varchar(100), GETDATE(), 110): 05-16-2006
Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16
Select CONVERT(varchar(100), GETDATE(), 112): 20060516
Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513
Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547
Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49
Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700
Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827
Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM
Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM
2、Sql与oracle集区别
Sqlserver中except差集，intersect 交集,union并集，
select * from a except( select a.mail,a.name,a.pass,a.time from a inner join c on a.mail=c.mail and a.name=c.name and a.pass=c.pass)
Oracle中minus差集，intersect交集，union并集
select * from a minus( select a.mail,a.name,a.pass,a.time from a inner join c on a.mail=c.mail and a.name=c.name and a.pass=c.pass)
merge用法： Merge into table1 Using table2 on
(table1.id=table2.id and table1.name=table2.name) When matched then
Update set table1.name=table2.name,table1.mail=table2.mail When not matched then
Insert (id,name,mail) values(table2.id,table2.name,table2.mail)
3、数学函数
序号 函数功能 1 2 3 4 5 6 7 8 9 10 11 12 绝对值 取整(大) 取整(小) 取整（截取） 四舍五入 为底的幂 取e为底的对数 取10为底对数 取平方 取平方根 求任意数为底的幂 取随机数 SQL Server用法 select abs(-1) value select ceiling(-1.001) value select floor(-1.001) value select cast(-1.002 as int) value select round(1.23456,4) value select Exp(1) value select log(2.7182818284590451) select log10(10) select SQUARE(4) select SQRT(4) select power(3,4) select rand() Oracle用法 select abs(-1) value from dual select ceil(-1.001) value from dual select floor(-1.001) value from dual select trunc(-1.002) value from dual select round(1.23456,4) value from dual select Exp(1) value from dual select ln(2.7182818284590451) select log(10,10) select power(4,2) select SQRT(4) select power(3,4) select sys.dbms_random.value(0,1) 13 14 15 取符号 圆周率 sin,cos,tan select sign(-8) SELECT PI() select sin(PI()/2) select max(value) value from (select 1 value union 16 求集合最大值 select -2 value union select 4 value union select 3 value)a select min(value) value from (select 1 value union 17 求集合最小值 select -2 value union select 4 value union select 3 value)a 18 处理null值(F2中select F1,IsNull(F2,10) value select F1,nvl(F2,10) value from Tbl select least(1,-2,4,3) value from dual select greatest(1,-2,4,3) value from dual select sign(-8) select sin(PI()/2) 的null以10代替) from Tbl 4、数值间比较
序号 函数功能 1 2 3 4 求字符序号 从序号求字符 连接 子串位置 模糊子串的位(返5 回2,参数去掉中间%则返回7) SQL Server用法 select ascii('a') select char(97) value select '11'+'22'+'33' value select charindex('s','sdsq',2) value select patindex('%d%q%','sdsfasdqe') value Oracle用法 select ascii('a') value from dual select chr(97) value from dual select CONCAT('11','22')||33 value from dual select instr('sdsq','s',2) value from dual select INSTR('sdsfasdqe','sd',1,2) value from dual 返回6 6 7 求子串 子串代替(返回aijklmnef) 子串全部替换 长度 大小写转换 单词首字母大写 左补空格 右补空格 删除空格 重复字符串 发音相似性比较select substring('abcd',2,2) value SELECT STUFF('abcdef', 2, 3, 'ijklmn') value 没发现 Len或datalength lower,upper 没发现 select substr('abcd',2,2) value from dual SELECT Replace('abcdef', 'bcd', 'ijklmn') value from dual select Translate('fasdbfasegas','fa','我' ) value from dual 8 9 10 11 12 13 14 15 length lower,upper select INITCAP('abcd dsaf df') value from dual select LPAD('abcd',14) value from dual select RPAD('abcd',14) value from dual ltrim,rtrim,trim lpad('d',6,'0'),rpad(‘d’,6,’0’) SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') from dual select space(10)+'abcd' value select 'abcd'+space(10) value ltrim,rtrim select REPLICATE('abcd',2) value SELECT SOUNDEX ('Smith'), SOUNDEX ('Smythe') 16 (这两个单词返回值一样，发音相同) 5、日期函数
序号 1 函数功能 系统时间 前后几日 SQL Server用法 select getdate() value 直接与整数相加减 Os直s 求日期 select convert(char(10),getdate(),20) value dtov 求时间 select convert(char(8),getdate(),108) value stov