oracle 日期处理大全.docx

1、oracle 日期处理大全oracle 日期函数网上已经有了不少，特我们跟集中一下，免得大家麻烦。在oracle数据库的开发中，常因为时间的问题大费周章，所以特地将ORACLE数据的日期函数收藏致此。乃供他日所查也。 1、add_months(d,n) 日期d加n个月 SQL SELECT SYSDATE AS This_Day,add_months(SYSDATE,1) AS Next_Day FROM dual; THIS_DAY NEXT_DAY - - 08-9月 -10 08-10月-102、last_day(d) 包含d的月份的最后一天的日期 SQL select last_day

2、(sysdate) as last_day from dual; LAST_DAY - 30-9月 -103、new_time(d,a,b) 时区的日期和时间d在b时区的日期和时间 SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) as china, to_char(new_time(sysdate,est,GMT),YYYY-MM-DD HH24:MI:SS) as GMT from dual; CHINA GMT - - 2010-09-08 09:51:50 2010-09-08 14:51:504、next_day(d,day) 比日期

3、d晚，由day指定的周几的日期 SQL select sysdate as this_day, next_day(sysdate,7) as next_sat from dual; THIS_DAY NEXT_SAT - - 08-9月 -10 11-9月 -105、sysdate 当前的系统日期和时间6、greatest(d1,d2,.dn) 给出的日期列表中最后的日期 SQL select sysdate as this_day,greatest(sysdate,sysdate+1,sysdate+2) as max_day from dual; THIS_DAY MAX_DAY - -

4、08-9月 -10 10-9月 -107、least(d1,k2,.dn) 给出的日期列表中最早的日期 SQL select sysdate as this_day,least(sysdate,sysdate+1,sysdate-1) as min_day from dual; THIS_DAY MIN_DAY - - 08-9月 -10 07-9月 -108、to_char(d ,fmt) 日期d按fmt指定的格式转变成字符串 SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) as today from dual; TODAY - 2010-

5、09-08 10:37:089、to_date(st ,fmt) 字符串st按fmt指定的格式转成日期值，若fmt忽略，st要用缺省格式 SQL select to_date(2010-09-08 10:37:08,YYYY-MM-DD HH24:MI:SS) as today from dual; TODAY - 08-9月 -1010、round(d ,fmt) 日期d按fmt指定格式舍入到最近的日期 SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) as this ,round(sysdate) as round_day ,to_char

6、(sysdate+1/6,YYYY-MM-DD HH24:MI:SS) as next_4hour ,round(sysdate+1/6) as round_day from dual; THIS ROUND_DAY NEXT_4HOUR ROUND_DAY - - - - 2010-09-08 10:52:45 08-9月 -10 2010-09-08 14:52:45 09-9月 -1011、trunc(d ,fmt) 日期d按fmt指定格式截断到最近的日期 SQL select to_char(sysdate,YYYY-MM-DD HH24:MI:SS) as this ,trunc(s

7、ysdate) as trunc_day ,to_char(sysdate+1/6,YYYY-MM-DD HH24:MI:SS) as next_4hour ,trunc(sysdate+1/6) as trunc_day from dual; THIS TRUNC_DAY NEXT_4HOUR TRUNC_DAY - - - - 2010-09-08 10:56:18 08-9月 -10 2010-09-08 14:56:18 08-9月 -10to_date 字符串类型转为换日期类型 字符串中的相应位置上的字符,必须符合时间范围的限制 查询Oracle日期格式 - select * fro

8、m nls_database_parameters; 得到结果如下表: 表中NLS_DATE_FORMAT表示日期格式. PARAMETER VALUE - - NLS_LANGUAGE AMERICAN NLS_TERRITORY AMERICA NLS_CURRENCY $ NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET ZHS16GBK NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT DD-MON-RR NLS_DATE_LANGUAGE AMERICAN NLS_SOR

9、T BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZM NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZM NLS_DUAL_CURRENCY $ NLS_COMP BINARY NLS_NCHAR_CHARACTERSET ZHS16GBK NLS_RDBMS_VERSION 8.1.7.0.0 或者查询V$NLS_PARAMETER

10、S表, select * from V$NLS_PARAMETERS; 也有类似结果 SQLselect to_date(2004-11-12 12-07-32,yyyy-mm-dd hh24-mi-ss) value from dual; VALUE - 2004.11.12 12:07:32 SQLselect to_date(20041015) value from dual; VALUE - 2004.10.15 00:00:00 SQLselect to_date(20041315) value from dual; ERROR 位于第 1 行: ORA-01861: 文字与格式字符

11、串不匹配 sysdate 当前日期和时间 SQLselect sysdate value from dual; VALUE - 2003.11.23 17:09:01 last_day 本月最后一天 SQLselect last_day(sysdate) value from dual; VALUE - 2003.11.30 17:08:17 add_months(d,n) 日期d后推n个月 SQLselect add_months(sysdate,2) value from dual; VALUE - 2005.01.23 17:10:21 next_day(d,day) 日期d之后的第一周

12、中,指定的那天(指定星期的第几天)是什么日期 SQLselect next_day(sysdate,1) value from dual; VALUE - 2004.11.28 17:38:55 oracle/plsqloracle日期处理完全版 日期处理完全版 TO_DATE格式 Day: dd number 12 dy abbreviated fri day spelled out friday ddspth spelled out, ordinal twelfth Month: mm number 03 mon abbreviated mar month spelled out marc

13、h Year: yy two digits 98 yyyy four digits 1998 24小时格式下时间范围为： 0:00:00 - 23:59:59. 12小时格式下时间范围为： 1:00:00 - 12:59:59 . 1. 日期和字符转换函数用法（to_date,to_char） 2. select to_char( to_date(222,J),Jsp) from dual 显示Two Hundred Twenty-Two 3. 求某天是星期几 select to_char(to_date(2002-08-26,yyyy-mm-dd),day) from dual; 星期一 s

14、elect to_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE = American) from dual; monday 设置日期语言 ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN; 也可以这样 TO_DATE (2002-08-26, YYYY-mm-dd, NLS_DATE_LANGUAGE = American) 4. 两个日期间的天数 select floor(sysdate - to_date(20020405,yyyymmdd) from dual; 5. 时间为nu

15、ll的用法 select id, active_date from table1 UNION select 1, TO_DATE(null) from dual; 注意要用TO_DATE(null) 6. a_date between to_date(20011201,yyyymmdd) and to_date(20011231,yyyymmdd) 那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。 所以，当时间需要精确的时候，觉得to_char还是必要的 7. 日期格式冲突问题 输入的格式要看你安装的ORACLE字符集的类型, 比如: US7ASCII, dat

16、e格式的类型就是: 01-Jan-01 alter system set NLS_DATE_LANGUAGE = American alter session set NLS_DATE_LANGUAGE = American 或者在to_date中写 select to_char(to_date(2002-08-26,yyyy-mm-dd),day,NLS_DATE_LANGUAGE = American) from dual; 注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多， 可查看 select * from nls_session_parameters select *

17、 from V$NLS_PARAMETERS 8. select count(*) from ( select rownum-1 rnum from all_objects where rownum select sysdate ,to_char(sysdate,hh) from dual; SYSDATE TO_CHAR(SYSDATE,HH) - - 2003-10-13 19:35:21 07 SQL select sysdate ,to_char(sysdate,hh24) from dual; SYSDATE TO_CHAR(SYSDATE,HH24) - - 2003-10-13

18、19:35:21 19 获取年月日与此类似 13. 年月日的处理 select older_date, newer_date, years, months, 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, newer_date

19、, older_date from ( select hiredate older_date, add_months(hiredate,rownum)+rownum newer_date from emp ) ) 14. 处理月份天数不定的办法 select to_char(add_months(last_day(sysdate) +1, -2), yyyymmdd),last_day(sysdate) from dual 16. 找出今年的天数 select add_months(trunc(sysdate,year), 12) - trunc(sysdate,year) from dual

20、 闰年的处理方法 to_char( last_day( to_date(02 | :year,mmyyyy) ), dd ) 如果是28就不是闰年 17. yyyy与rrrr的区别 YYYY99 TO_C - - yyyy 99 0099 rrrr 99 1999 yyyy 01 0001 rrrr 01 2001 18.不同时区的处理 select to_char( NEW_TIME( sysdate, GMT,EST), dd/mm/yyyy hh:mi:ss) ,sysdate from dual; 19. 5秒钟一个间隔 Select TO_DATE(FLOOR(TO_CHAR(sys

21、date,SSSSS)/300) * 300,SSSSS) ,TO_CHAR(sysdate,SSSSS) from dual 2002-11-1 9:55:00 35786 SSSSS表示5位秒数 20. 一年的第几天 select TO_CHAR(SYSDATE,DDD),sysdate from dual 310 2002-11-6 10:03:51 21.计算小时,分,秒,毫秒 select Days, A, TRUNC(A*24) Hours, TRUNC(A*24*60 - 60*TRUNC(A*24) Minutes, TRUNC(A*24*60*60 - 60*TRUNC(A*

22、24*60) Seconds, TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60) mSeconds from ( select trunc(sysdate) Days, sysdate - trunc(sysdate) A from dual ) select * from tabname order by decode(mode,FIFO,1,-1)*to_char(rq,yyyymmddhh24miss); / floor(date2-date1) /365) 作为年 floor(date2-date1, 365) /30) 作为月 mod(mod(

23、date2-date1, 365), 30)作为日. 23.next_day函数 next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。 很简单的一句话。 把Oracle的日期当作一个特殊数字，以天为单位。 可以进行 日期+数字 = 日期， 日期-日期=数字，日期-数字=日期 MESTAMP数据的格式化显示和DATE 数据一样。注意，to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型。这已经清楚表明了在当两个时间的差别极度重要的情况下，使用TIMESTAMP数据类型要比DATE数据类型更确切。 如果你想显

24、示TIMESTAMP的小数秒信息，参考下面： 1SELECT TO_CHAR(time1,MM/DD/YYYY HH24:MI:SS:FF3) Date FROM date_table Date - 06/20/2003 16:55:14:000 06/26/2003 11:16:36:000 在上例中，我只现实了小数点后3位的内容。 计算timestamp间的数据差别要比老的date数据类型更容易。当你直接相减的话，看看会发生什么。结果将更容易理解，第一行的17天，18小时，27分钟和43秒。 1SELECT time1, 2 time2, 3 substr(time2-time1),instr(time2-time1), )+7,2) seconds, 4 substr(time2-time1),instr(time2-time1), )+4,2) minutes, 5 substr(time2-time1),instr(time2-time1), )+1,2) hours, 6 trunc(to_number(substr(time2-time1),1,instr(time2-time1,