Oracle时间处理大全

SQL Server和Oracle常用时间日期处理语句在SQL语句中,常常用会对时间(或日期)进行一些处理,下面是比较通用的一些语句:延迟:view plainc opy to clipboardprint?1.sysdate+(5/24/60/60) 在系统时间基础上延迟5秒2.3.sysdate+5/24/60 在系统时间基础上延迟5分钟4.5.sysdate+5/24 在系统时间基础上延迟5小时6.7.sysdate+5 在系统时间基础上延迟5天8.9.add_months(sysdate,-5) 在系统时间基础上延迟5月10.11.add_months(sysdate,-5*12) 在系统时间基础上延迟5年上月末的日期:view plainc opy to clipboardprint?1.select last_day(add_months(sysdate, -1)) from dual;本月的最后一秒:view plainc opy to clipboardprint?1.select trunc(add_months(sysdate,1),'MM') - 1/24/60/60 from dual本周星期一的日期:view plainc opy to clipboardprint?1.select trunc(sysdate,'day')+1 from dual年初至今的天数:view plainc opy to clipboardprint?1.select ceil(sysdate - trunc(sysdate, 'year')) from dual;今天是今年的第几周 :view plainc opy to clipboardprint?1.select to_char(sysdate,'fmww') from dual今天是本月的第几周:view plainc opy to clipboardprint?1.SELECT TO_CHAR(SYSDATE,'WW') - TO_CHAR(TRUNC(SYSDATE,'MM'),'WW') + 1 AS"weekOfMon"FROM dual本月的天数view plainc opy to clipboardprint?1.SELECT to_char(last_day(SYSDATE),'dd') days FROM dual今年的天数view plainc opy to clipboardprint?1.select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 下个星期一的日期view plainc opy to clipboardprint?1.SELECT Next_day(trunc(SYSDATE),'monday') FROM dual＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝计算工作日方法view plainc opy to clipboardprint?1.create table t(s date,e date);2.3.alter session set nls_date_format = 'yyyy-mm-dd';4.5.insert into t values('2003-03-01','2003-03-03');6.7.insert into t values('2003-03-02','2003-03-03');8.9.insert into t values('2003-03-07','2003-03-08');10.11.insert into t values('2003-03-07','2003-03-09');12.13.insert into t values('2003-03-05','2003-03-07');14.15.insert into t values('2003-02-01','2003-03-31');16.17.-- 这里假定日期都是不带时间的,否则在所有日期前加trunc即可.18.19.select s,e,e-s+1 total_days, trunc((e-s+1)/7)*5 +length(replace(substr('01111100111110',to_char(s,'d'),mod(e-s+1,7)),'0','')) work_days from t;20.21.-- drop table t;====================================================判断当前时间是上午下午还是晚上view plainc opy to clipboardprint?1.SELECT CASE2.WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 6 AND 11 THEN'上午'3.4.WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 11 AND 17 THEN'下午'5.6.WHEN to_number(to_char(SYSDATE,'hh24')) BETWEEN 17 AND 21 THEN'晚上'7.8.END9.10.FROM dual;===================================================Oracle 中的一些处理日期将数字转换为任意时间格式.如秒:需要转换为天/小时view plainc opy to clipboardprint?1.SELECT to_char(floor(TRUNC(936000/(60*60))/24))||'天'||to_char(mod(TRUNC(936000/(60*60)),24))||'小时'FROM DUALTO_DATE格式view plainc opy to clipboardprint?1.Day:2.3.dd number 124.5.dy abbreviated fri6.7.day spelled out friday8.9.ddspth spelled out, ordinal twelfth10.11.Month:12.13.mm number 0314.15.mon abbreviated mar16.17.month spelled out march18.19.Year:20.21.yy two digits 9822.23.yyyy four digits 199824小时格式下时间范围为: 0:00:00 – 23:59:59….12小时格式下时间范围为: 1:00:00 – 12:59:59 ….1. 日期和字符转换函数用法(to_date,to_char)2.view plainc opy to clipboardprint?1.select to_char( to_date(222,'J'),'Jsp') from dual显示Two Hundred Twenty-Two3. 求某天是星期几view plainc opy to clipboardprint?1.select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day') from dual;2.3.select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE =American') from dual;设置日期语言view plainc opy to clipboardprint?1.ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN';也可以这样view plainc opy to clipboardprint?1.TO_DATE ('2002-08-26', 'YYYY-mm-dd', 'NLS_DATE_LANGUAGE = American')4. 两个日期间的天数view plainc opy to clipboardprint?1.select floor(sysdate - to_date('20020405','yyyymmdd')) from dual;5. 时间为null的用法view plainc opy to clipboardprint?1.select id, active_date from table12.UNION3.select 1, TO_DATE(null) from dual;注意要用TO_DATE(null)6.view plainc opy to clipboardprint?1.a_date between to_date('20011201','yyyymmdd') andto_date('20011231','yyyymmdd')那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的.所以,当时间需要精确的时候,觉得to_char还是必要的7. 日期格式冲突问题输入的格式要看你安装的ORACLE字符集的类型, 比如:US7ASCII, date格式的类型就是: ’01-Jan-01′view plainc opy to clipboardprint?1.alter system set NLS_DATE_LANGUAGE = American2.3.alter session set NLS_DATE_LANGUAGE = American或者在to_date中写view plainc opy to clipboardprint?1.select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE =American') from dual;注意我这只是举了NLS_DATE_LANGUAGE,当然还有很多, 可查看view plainc opy to clipboardprint?1.select * from nls_session_parameters2.select * from V$NLS_PARAMETERS8.view plainc opy to clipboardprint?1.select count(*)2.from ( select rownum-1 rnum3.from all_objects4.where rownum <= to_date('2002-02-28','yyyy-mm-dd') -to_date('2002-02-01','yyyy-mm-dd')+1 )5.where to_char( to_date('2002-02-01','yyyy-mm-dd')+rnum-1, 'D' )6.not in ( '1', '7' )查找2002-02-28至2002-02-01间除星期一和七的天数在前后分别调用DBMS_UTILITY.GET_TIME, 然后将结果相减(得到的是1/100秒, 而不是毫秒).9.view plainc opy to clipboardprint?1.select months_between(to_date('01-31-1999','MM-DD-YYYY'),2.to_date('12-31-1998','MM-DD-YYYY')) "MONTHS"FROM DUAL;3. 14.select months_between(to_date('02-01-1999','MM-DD-YYYY'),5.to_date('12-31-1998','MM-DD-YYYY')) "MONTHS"FROM DUAL;6. 1.0322580645161310. Next_day的用法view plainc opy to clipboardprint?1.Next_day(date, day)2.3.Monday-Sunday, for format code DAY4.Mon-Sun, for format code DY5.1-7, for format code D11view plainc opy to clipboardprint?1.select to_char(sysdate,'hh:mi:ss') TIME from all_objects注意:第一条记录的TIME 与最后一行是一样的可以建立一个函数来处理这个问题view plainc opy to clipboardprint?1.create or replace function sys_date return date is2.begin3.return sysdate;4.end;5.select to_char(sys_date,'hh:mi:ss') from all_objects;12. 获得小时数view plainc opy to clipboardprint?1.SQL> select sysdate ,to_char(sysdate,'hh') from dual;2.SYSDATE TO_CHAR(SYSDATE,'HH')——————–———————2003-10-13 19:35:21 07view plainc opy to clipboardprint?1.SQL> select sysdate ,to_char(sysdate,'hh24') from dual;2.3.SYSDATE TO_CHAR(SYSDATE,'HH24')——————–———————–2003-10-13 19:35:21 19获取年月日与此类似13. 年月日的处理view plainc opy to clipboardprint?1.select older_date,2.newer_date,3.years,4.months,5.6.abs( trunc( newer_date-add_months( older_date,years*12+months ) ) days from( select trunc(months_between( newer_date, older_date )/12) YEARS,mod(trunc(months_between( newer_date, older_date )),12 ) MONTHS,view plainc opy to clipboardprint?1.newer_date,2.older_date3.from ( select hiredate older_date,4.add_months(hiredate,rownum)+rownum newer_date from emp ) )14. 处理月份天数不定的办法view plainc opy to clipboardprint?1.select to_char(add_months(last_day(sysdate) +1, -2),'yyyymmdd'),last_day(sysdate) from dual16. 找出今年的天数view plainc opy to clipboardprint?1.select add_months(trunc(sysdate,'year'), 12) - trunc(sysdate,'year') from dual 闰年的处理方法view plainc opy to clipboardprint?1.to_char( last_day( to_date('02' | | :year,'mmyyyy') ), 'dd' )如果是28就不是闰年17. yyyy与rrrr的区别‘YYYY99 TO_C——- —-yyyy 99 0099rrrr 99 1999yyyy 01 0001rrrr 01 200118.不同时区的处理view plainc opy to clipboardprint?1.select to_char( NEW_TIME( sysdate, 'GMT','EST'), 'dd/mm/yyyy hh:mi:ss') ,sysdate2.from dual;19. 5秒钟一个间隔view plainc opy to clipboardprint?1.Select TO_DATE(FLOOR(TO_CHAR(sysdate,'SSSSS')/300) *300,'SSSSS') ,TO_CHAR(sysdate,'SSSSS')2.from dualTO_DATE(FL TO_CH———- —–2007-01-01 60368SSSSS表示5位秒数20. 一年的第几天view plainc opy to clipboardprint?1.select TO_CHAR(SYSDATE,'DDD'),sysdate from dualTO_ SYSDATE————-017 2007-01-1721.计算小时,分,秒,毫秒view plainc opy to clipboardprint?1.select2.Days,3.A,4.TRUNC(A*24) Hours,5.TRUNC(A*24*60 - 60*TRUNC(A*24)) Minutes,6.TRUNC(A*24*60*60 - 60*TRUNC(A*24*60)) Seconds,7.TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60)) mSeconds8.from9.( select trunc(sysdate) Days, sysdate - trunc(sysdate) A10.from dual11.)12.13.select * from tabname14.order by decode(mode,'FIFO',1,-1)*to_char(rq,'yyyymmddhh24miss');15.16.//17.18.floor((date2-date1) /365) 作为年19.floor((date2-date1, 365) /30) 作为月20.mod(mod(date2-date1, 365), 30) 作为日.22.next_day函数next_day(sysdate,6)是从当前开始下一个星期五.后面的数字是从星期日开始算起.1 2 3 4 5 6 7日一二三四五六—————————————————————view plainc opy to clipboardprint?1.select (sysdate-to_date('2003-12-03 12:55:45','yyyy-mm-ddhh24:mi:ss'))*24*60*60 from dual日期返回的是天然后转换为ss。