1、通常用到的字符串转日期格式Select CONVERT(varchar(100), GETDATE(), 0): 05 16 2006 10:57AM Select CONVERT(varchar(100), GETDATE(), 1): 05/16/06Select CONVERT(varchar(100), GETDATE(), 2): 06.05.16Select CONVERT(varchar(100), GETDATE(), 3): 16/05/06Select CONVERT(varchar(100), GETDATE(), 4): 16.05.06Select CONVERT(varchar(100), GETDATE(), 5): 16-05-06Select CONVERT(varchar(100), GETDATE(), 6): 16 05 06Select CONVERT(varchar(100), GETDATE(), 7): 05 16, 06Select CONVERT(varchar(100), GETDATE(), 8): 10:57:46Select CONVERT(varchar(100), GETDATE(), 9): 05 16 2006 10:57:46:827AM Select CONVERT(varchar(100), GETDATE(), 10): 05-16-06Select CONVERT(varchar(100), GETDATE(), 11): 06/05/16Select CONVERT(varchar(100), GETDATE(), 12): 060516Select CONVERT(varchar(100), GETDATE(), 13): 16 05 2006 10:57:46:937 Select CONVERT(varchar(100), GETDATE(), 14): 10:57:46:967Select 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-16Select CONVERT(varchar(100), GETDATE(), 24): 10:57:47Select CONVERT(varchar(100), GETDATE(), 25): 2006-05-16 10:57:47.250Select CONVERT(varchar(100), GETDATE(), 100): 05 16 2006 10:57AMSelect CONVERT(varchar(100), GETDATE(), 101): 05/16/2006Select CONVERT(varchar(100), GETDATE(), 102): 2006.05.16Select CONVERT(varchar(100), GETDATE(), 103): 16/05/2006Select CONVERT(varchar(100), GETDATE(), 104): 16.05.2006Select CONVERT(varchar(100), GETDATE(), 105): 16-05-2006Select CONVERT(varchar(100), GETDATE(), 106): 16 05 2006Select CONVERT(varchar(100), GETDATE(), 107): 05 16, 2006Select CONVERT(varchar(100), GETDATE(), 108): 10:57:49Select CONVERT(varchar(100), GETDATE(), 109): 05 16 2006 10:57:49:437AMSelect CONVERT(varchar(100), GETDATE(), 110): 05-16-2006Select CONVERT(varchar(100), GETDATE(), 111): 2006/05/16Select CONVERT(varchar(100), GETDATE(), 112): 20060516Select CONVERT(varchar(100), GETDATE(), 113): 16 05 2006 10:57:49:513Select CONVERT(varchar(100), GETDATE(), 114): 10:57:49:547Select CONVERT(varchar(100), GETDATE(), 120): 2006-05-16 10:57:49Select CONVERT(varchar(100), GETDATE(), 121): 2006-05-16 10:57:49.700Select CONVERT(varchar(100), GETDATE(), 126): 2006-05-16T10:57:49.827Select CONVERT(varchar(100), GETDATE(), 130): 18 ???? ?????? 1427 10:57:49:907AM Select CONVERT(varchar(100), GETDATE(), 131): 18/04/1427 10:57:49:920AM2、Sql与oracle集区别Sqlserver中except差集，intersect交集,union并集，select*from a except(select a.mail,,a.pass,a.time from a inner join c ona.mail=c.mail and = and a.pass=c.pass)Oracle中minus差集，intersect交集，union并集select*from a minus(select a.mail,,a.pass,a.time from a inner join c ona.mail=c.mail and = and a.pass=c.pass)merge用法：Merge into table1Using table2 on(table1.id=table2.id and =)When matched thenUpdate set =,table1.mail=table2.mailWhen not matched thenInsert (id,name,mail) values(table2.id,,table2.mail)3、数学函数序号函数功能SQL Server用法Oracle用法1绝对值select abs(-1) value select abs(-1) value from dual2取整(大)select ceiling(-1.001) value select ceil(-1.001) value from dual3取整(小)select floor(-1.001) value select floor(-1.001) value from dual4取整（截取）select cast(-1.002 as int)valueselect trunc(-1.002) value fromdual5四舍五入select round(1.23456,4) value select round(1.23456,4) value from dual6为底的幂select Exp(1) value select Exp(1) value from dual7取e为底的对数selectlog(2.7182818284590451)select ln(2.7182818284590451)8取10为底对数select log10(10)select log(10,10) 9取平方select SQUARE(4)select power(4,2) 10取平方根select SQRT(4)select SQRT(4) 11求任意数为底的幂select power(3,4)select power(3,4) 12取随机数select rand()selectsys.dbms_random.value(0,1) 13取符号select sign(-8)select sign(-8)14圆周率SELECT PI()15sin,cos,tan select sin(PI()/2)select sin(PI()/2)16求集合最大值select max(value) value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)aselect greatest(1,-2,4,3) valuefrom dual17求集合最小值select min(value) value from(select 1 valueunionselect -2 valueunionselect 4 valueunionselect 3 value)aselect least(1,-2,4,3) value fromdual18处理null值(F2中的null以10代替)select F1,IsNull(F2,10) valuefrom Tblselect F1,nvl(F2,10) value fromTbl4、数值间比较序号函数功能SQL Server用法Oracle用法1求字符序号select ascii('a')select ascii('a') value from dual 2从序号求字符select char(97) value select chr(97) value from dual3连接select '11'+'22'+'33' value select CONCAT('11','22')||33 value from dual4子串位置select charindex('s','sdsq',2)valueselect instr('sdsq','s',2) valuefrom dual5模糊子串的位(返回2,参数去掉中间%则返回7)selectpatindex('%d%q%','sdsfasdqe') valueselect INSTR('sdsfasdqe','sd',1,2)value from dual 返回66求子串select substring('abcd',2,2)valueselect substr('abcd',2,2) valuefrom dual7子串代替(返回aijklmnef)SELECT STUFF('abcdef', 2, 3,'ijklmn') valueSELECT Replace('abcdef', 'bcd','ijklmn') value from dual8子串全部替换没发现selectTranslate('fasdbfasegas','fa','我' ) value from dual9长度Len或datalength length10大小写转换lower,upper lower,upper11单词首字母大写没发现select INITCAP('abcd dsaf df') value from dual12左补空格select space(10)+'abcd' value select LPAD('abcd',14) value from dual13右补空格select 'abcd'+space(10) value select RPAD('abcd',14) value from dual14删除空格ltrim,rtrim ltrim,rtrim,trim15重复字符串select REPLICATE('abcd',2)valuelpa d('d',6,'0'),rpad(‘d’,6,’0’)16发音相似性比较(这两个单词返回值一样，发音相同)SELECT SOUNDEX ('Smith'),SOUNDEX ('Smythe')SELECT SOUNDEX ('Smith'),SOUNDEX ('Smythe') from dual5、日期函数序号函数功能SQL Server用法O 1系统时间select getdate() value s 前后几日直接与整数相加减直求日期select convert(char(10),getdate(),20) valuesdtov求时间select convert(char(8),getdate(),108) valuestov取日期时间的其他部分DATEPART 和 DATENAME 函数（第一个参数决定）to参数---------------------------------需要补充year yy, yyyyquarter qq, q (季度)month mm, m (m O无效)dayofyear dy, y (O表星期)day dd, d (d O无效)week wk, ww (wk O无效)weekday dw (O不清楚)Hour hh,hh12,hh24 (hh12,hh24 S无效) minute mi, n (n O无效)second ss, s (s O无效)millisecond ms (O无效)当月最后一天没发现s fr本星期的某一天（比如星期日）没发现s F字符串转时间可以直接转或者select cast('2004-09-08'as datetime) values2h求两日期某一部分的差（比如秒）selectdatediff(ss,getdate(),getdate()+12.3) value直dv根据差值求新的日期（比如分钟）select dateadd(mi,8,getdate()) value S F6、日期转换日期函数日期转字符串(yyyymmddhhmiss)Sql server Getdate() select CONVERT(varchar(100),GETDATE(),112)+replace(CONVERT(varchar(100),GETDATE(),24),':','')Oracle sysdate select to_char(SYSDA TE,'YYYYMMDDHH24MISS')fromdualGbase Now() select to_char(now(),'YYYYMMDDHH24MISS')7、SQL-BULK Insert①将文本导入SQLBULK INSERT USERINFO--表名FROM'E:\Z_资料\SSIS_TxtToDB\USERINFO.TXT'--文件路径WITH(FIELDTERMINATOR='|', --列分隔符ROWTERMINATOR=',' --行分隔符)内容：1|张三|我是张三|男,2|李四|我是李四|女②按照模板导入SQL根据表建模板：bcp [数据库名].dbo.[表名] format nul -c -t, -f [输出文件路径]\[文件名] -S [服务器名] -U [帐号] -P [密码]BULK INSERT USERINFOFROM'E:\Z_资料\SSIS_TxtToDB\USERINFO.TXT'WITH(FORMATFILE='D:\a.fmt')Format 内容：9.041 SQLCHAR 0 1 "" 1 uid Chinese_PRC_CI_AS2 SQLCHAR 0 2 "" 2 uname Chinese_PRC_CI_AS3 SQLCHAR 04 "" 3 udesc Chinese_PRC_CI_AS4 SQLCHAR 0 1 "\r\n" 4 sid Chinese_PRC_CI_AS文本内容：1111111122222222③按模板查询语句SELECT*FROM OPENROWSET(BULK'E:\Z_资料\SSIS_TxtToDB\USERINFO.TXT', FORMATFILE='D:\b.xml')as t1;。