oracle 日期处理大全.docx

1、oracle 日期处理大全oracle日期处理大全.txt你无法改变别人，但你可以改变自己；你无法改变天气，但你可以改变心情；你无法改变生命长度，但你可以拓展它的宽度。oracle日期处理大全.txt18拥有诚实，就舍弃了虚伪；拥有诚实，就舍弃了无聊；拥有踏实，就舍弃了浮躁，不论是有意的丢弃，还是意外的失去，只要曾经真实拥有，在一些时候，大度舍弃也是一种境界。oracle日期函数网上已经有了不少，特我们跟集中一下，免得大家麻烦。在oracle数据库的开发中，常因为时间的问题大费周章，所以特地将ORACLE数据的日期函数收藏致此。乃供他日所查也。1、add_months(d,n)日期d加n个月S

2、QL 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(sysdate) as last_day from dual;LAST_DAY-30-9月-103、new_time(d,a,b)时区的日期和时间d在b时区的日期和时间SQL select to_char(sysdate,YY-MM-DD HH24:MI:SS) as china,

3、to_char(new_time(sysdate,est,GMT),YY-MM-DDHH24:MI:SS)asGMTfrom dual;CHINA GMT- -2010-09-08 09:51:50 2010-09-08 14:51:504、next_day(d,day)比日期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

4、,.dn)给出的日期列表中最后的日期SQLselectsysdateasthis_day,greatest(sysdate,sysdate+1,sysdate+2)asmax_day from dual;THIS_DAY MAX_DAY- -08-9月-10 10-9月-107、least(d1,k2,.dn)给出的日期列表中最早的日期SQL select sysdate as this_day,least(sysdate,sysdate+1,sysdate-1) as min_dayfrom dual;THIS_DAY MIN_DAY- -08-9月-10 07-9月-108、to_char

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

6、L select to_char(sysdate,YY-MM-DD HH24:MI:SS) as this,round(sysdate) as round_day,to_char(sysdate+1/6,YY-MM-DD HH24:MI:SS) as next_4hour,round(sysdate+1/6) as round_dayfrom 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

7、指定格式截断到最近的日期SQL select to_char(sysdate,YY-MM-DD HH24:MI:SS) as this,trunc(sysdate) as trunc_day,to_char(sysdate+1/6,YY-MM-DD HH24:MI:SS) as next_4hour,trunc(sysdate+1/6) as trunc_dayfrom 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字符

8、串类型转为换日期类型字符串中的相应位置上的字符,必须符合时间范围的限制查询Oracle日期格式-select * from nls_database_parameters;得到结果如下表:表中NLS_DATE_FORMAT表示日期格式.PARAMETER VALUE- -NLS_LANGUAGE AMERICANNLS_TERRITORY AMERICANLS_CURRENCY $NLS_ISO_CURRENCY AMERICANLS_NUMERIC_CHARACTERS .,NLS_CHARACTERSET ZHS16GBKNLS_CALENDAR GREGORIANNLS_DATE_FOR

9、MAT DD-MON-RRNLS_DATE_LANGUAGE AMERICANNLS_SORT BINARYNLS_TIME_FORMAT HH.MI.SSXFF AMNLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AMNLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZH:TZMNLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZH:TZMNLS_DUAL_CURRENCY $NLS_COMP BINARYNLS_NCHAR_CHARACTERSET ZHS16GBKNLS_RDBMS

10、_VERSION 8.1.7.0.0或者查询V$NLS_PARAMETERS表,select * from V$NLS_PARAMETERS;也有类似结果SQLselectto_date(2004-11-12-07-32,yy-mm-ddhh24-mi-ss)valuefromdual;VALUE-2004.11.12 12:07:32SQLselect to_date() value from dual;VALUE-2004.10.15 00:00:00SQLselect to_date() value from dual;ERROR位于第1行:ORA-01861:文字与格式字符串不匹配sy

11、sdate当前日期和时间SQLselect sysdate value from dual;VALUE-2003.11.23 17:09:01last_day本月最后一天SQLselect last_day(sysdate) value from dual;VALUE-2003.11.30 17:08:17add_months(d,n)日期d后推n个月SQLselect add_months(sysdate,2) value from dual;VALUE-2005.01.23 17:10:21next_day(d,day)日期d之后的第一周中,指定的那天(指定星期的第几天)是什么日期SQLs

12、elect next_day(sysdate,1) value from dual;VALUE-2004.11.28 17:38:55oracle/plsqloracle日期处理完全版日期处理完全版TO_DATE格式Day:dd number 12dy abbreviated friday spelled out fridayddspth spelled out, ordinal twelfthMonth:mm number 03mon abbreviated marmonth spelled out marchYear:yy two digits 98yy four digits 19982

13、4小时格式下时间范围为：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-Two3.求某天是星期几select to_char(to_date(2002-08-26,yy-mm-dd),day) from dual;星期一selectto_char(to_date(2002-08-26,yy-mm-dd),day,NLS_DATE_LANGUAGE

14、=American) fromdual;monday设置日期语言ALTER SESSION SET NLS_DATE_LANGUAGE=AMERICAN;也可以这样TO_DATE (2002-08-26, YY-mm-dd, NLS_DATE_LANGUAGE = American)4.两个日期间的天数select floor(sysdate - to_date(,yymmdd) from dual;5.时间为null的用法select id, active_date from table1UNIONselect 1, TO_DATE(null) from dual;注意要用TO_DATE(n

15、ull)6.a_date between to_date(,yymmdd) and to_date(,yymmdd)那么12月31号中午12点之后和12月1号的12点之前是不包含在这个范围之内的。所以，当时间需要精确的时候，觉得to_char还是必要的7.日期格式冲突问题输入的格式要看你安装的ORACLE字符集的类型,比如: US7ASCII, date格式的类型就是:01-Jan-01alter system set NLS_DATE_LANGUAGE = Americanalter session set NLS_DATE_LANGUAGE = American或者在to_date中写se

16、lectto_char(to_date(2002-08-26,yy-mm-dd),day,NLS_DATE_LANGUAGE=American) fromdual;注意我这只是举了NLS_DATE_LANGUAGE，当然还有很多，可查看select * from nls_session_parametersselect * from V$NLS_PARAMETERS8.select count(*)from ( select rownum-1 rnumfrom all_objectswhere rownum select sysdate ,to_char(sysdate,hh) from du

17、al;SYSDATE TO_CHAR(SYSDATE,HH)- -2003-10-13 19:35:21 07SQL select sysdate ,to_char(sysdate,hh24) from dual;SYSDATE TO_CHAR(SYSDATE,HH24)- -2003-10-13 19:35:21 19获取_年_月_日与此类似13._年_月_日的处理select older_date,newer_date,years,months,abs(trunc(newer_date-add_months( older_date,years*12+months ) daysfrom (

18、selecttrunc(months_between( newer_date, older_date )/12) YEARS,mod(trunc(months_between( newer_date, older_date ),12 ) MONTHS,newer_date,older_datefrom ( select hiredate older_date,add_months(hiredate,rownum)+rownum newer_datefrom emp )14.处理月份天数不定的办法selectto_char(add_months(last_day(sysdate)+1,-2),y

19、ymmdd),last_day(sysdate)from dual16.找出今年的天数select add_months(trunc(sysdate,year), 12) - trunc(sysdate,year) from dual闰年的处理方法to_char( last_day( to_date(02 | :year,mmyy) ), dd )如果是28就不是闰年17.yy与rr的区别YY99 TO_C- -yy 99 0099rr 99 1999yy 01 0001rr 01 200118.不同时区的处理select to_char( NEW_TIME( sysdate, GMT,EST

20、), dd/mm/yy hh:mi:ss) ,sysdatefrom dual;19.5秒钟一个间隔SelectTO_DATE(FLOOR(TO_CHAR(sysdate,SSS)/300)*300,SSS) ,TO_CHAR(sysdate,SSS)from dual2002-11-1 9:55:00 35786SSS表示5位秒数20.一年的第几天select TO_CHAR(SYSDATE,DDD),sysdate from dual310 2002-11-6 10:03:5121.计算小时,分,秒,毫秒selectDays,A,TRUNC(A*24) Hours,TRUNC(A*24*6

21、0 - 60*TRUNC(A*24) Minutes,TRUNC(A*24*60*60 - 60*TRUNC(A*24*60) Seconds,TRUNC(A*24*60*60*100 - 100*TRUNC(A*24*60*60) mSecondsfrom(selecttrunc(sysdate) Days,sysdate - trunc(sysdate) Afrom dual)select * from tabnameorder by decode(mode,FIFO,1,-1)*to_char(rq,yymmddhh24miss);/floor(date2-date1) /365)作为年

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

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

24、e2-time1),instr(time2-time1),)+7,2)seconds,4substr(time2-time1),instr(time2-time1),)+4,2)minutes,5substr(time2-time1),instr(time2-time1),)+1,2)hours,6trunc(to_number(substr(time2-time1),1,instr(time2-time1, )days,7trunc(to_number(substr(time2-time1),1,instr(time2-time1,)/7) weeks8*FROM date_tableTIME1TIME2SECONDS MINUTES HOURSDAYS WEEKS- - - - -06/20/2003:16:55:14:00007/08/2003:11:22:57:817206/26/2003:11:16:36:00007/08/2003:11:22:57:0121这就意味着不再需要关心一天有多少秒在麻烦的计算中。因此，得到天数、月数、天数、时数、分钟数和秒数就成为用substr函数摘取出数字的事情了。系统日期和时间为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。SQL SELECT SYSDATE FR