日期和时间函数汇总.doc

日期和時間函數匯總oracle中处理日期大全2oracle中有很多关于日期的函数7與周相關日期函數10oracle中时间运算13oracle中处理日期大全 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 98yyyy four digits 199824小时格式下时间范围为： 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,yyyy-mm-dd),day) from dual;星期一select 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. 时间为null的用法select id, active_date from table1UNIONselect 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, date格式的类型就是: 01-Jan-01alter system set NLS_DATE_LANGUAGE = Americanalter 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_parametersselect * from V$NLS_PARAMETERS8.select count(*)from ( select rownum-1 rnumfrom all_objectswhere rownum select sysdate ,to_char(sysdate,hh) from dual;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 ( 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.处理月份天数不定的办法select to_char(add_months(last_day(sysdate) +1, -2), yyyymmdd),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,mmyyyy) ), dd )如果是28就不是闰年17.yyyy与rrrr的区别YYYY99 TO_C- -yyyy 99 0099rrrr 99 1999yyyy 01 0001rrrr 01 200118.不同时区的处理select to_char( NEW_TIME( sysdate, GMT,EST), dd/mm/yyyy hh:mi:ss) ,sysdatefrom dual;19.5秒钟一个间隔Select TO_DATE(FLOOR(TO_CHAR(sysdate,SSSSS)/300) * 300,SSSSS) ,TO_CHAR(sysdate,SSSSS)from dual2002-11-1 9:55:00 35786SSSSS表示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*60 - 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,yyyymmddhh24miss);/floor(date2-date1) /365) 作为年floor(date2-date1, 365) /30) 作为月mod(mod(date2-date1, 365), 30)作为日.23.next_day函数next_day(sysdate,6)是从当前开始下一个星期五。后面的数字是从星期日开始算起。1 2 3 4 5 6 7日 一 二 三 四 五 六oracle中有很多关于日期的函数在oracle中有很多关于日期的函数，如： 1、add_months()用于从一个日期值增加或减少一些月份 date_value:=add_months(date_value,number_of_months) 例： SQL select add_months(sysdate,12) Next Year from dual; Next Year - 13-11月-04 SQL select add_months(sysdate,112) Last Year from dual; Last Year - 13-3月 -13 SQL 2、current_date()返回当前会放时区中的当前日期 date_value:=current_date SQL column sessiontimezone for a15 SQL select sessiontimezone,current_date from dual; SESSIONTIMEZONE CURRENT_DA - - +08:00 13-11月-03 SQL alter session set time_zone=-11:00 2 / 会话已更改。 SQL select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP - - -11:00 12-11月-03 04.59.13.668000 下午 -11: 00 SQL 3、current_timestamp()以timestamp with time zone数据类型返回当前会放时区中的当前日期 timestamp_with_time_zone_value:=current_timestamp(timestamp_precision) SQL column sessiontimezone for a15 SQL column current_timestamp format a36 SQL select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP - - +08:00 13-11月-03 11.56.28.160000 上午 +08: 00 SQL alter session set time_zone=-11:00 2 / 会话已更改。 SQL select sessiontimezone,current_timestamp from dual; SESSIONTIMEZONE CURRENT_TIMESTAMP - - -11:00 12-11月-03 04.58.00.243000 下午 -11: 00 SQL 4、dbtimezone()返回时区 varchar_value:=dbtimezone SQL select dbtimezone from dual; DBTIME - -07:00 SQL 5、extract()找出日期或间隔值的字段值 date_value:=extract(date_field from datetime_value|interval_value) SQL select extract(month from sysdate) This Month from dual; This Month - 11 SQL select extract(year from add_months(sysdate,36) 3 Years Out from dual; 3 Years Out - 2006 SQL 6、last_day()返回包含了日期参数的月份的最后一天的日期 date_value:=last_day(date_value) SQL select last_day(date2000-02-01) Leap Yr? from dual; Leap Yr? - 29-2月 -00 SQL select last_day(sysdate) Last day of this month from dual; Last day o - 30-11月-03 SQL 7、localtimestamp()返回会话中的日期和时间 timestamp_value:=localtimestamp SQL column localtimestamp format a28 SQL select localtimestamp from dual; LOCALTIMESTAMP - 13-11月-03 12.09.15.433000 下午 SQL select localtimestamp,current_timestamp from dual; LOCALTIMESTAMP CURRENT_TIMESTAMP - - 13-11月-03 12.09.31.006000 13-11月-03 12.09.31.006000 下午 +08: 下午 00 SQL alter session set time_zone=-11:00; 会话已更改。 SQL select localtimestamp,to_char(sysdate,DD-MM-YYYY HH:MI:SS AM) SYSDATE from dual; LOCALTIMESTAMP SYSDATE - - 12-11月-03 05.11.31.259000 13-11-2003 12:11:31 下午 下午 SQL 8、months_between()判断两个日期之间的月份数量 number_value:=months_between(date_value,date_value) SQL select months_between(sysdate,date1971-05-18) from dual; MONTHS_BETWEEN(SYSDATE,DATE1971-05-18) - 389.855143 SQL select months_between(sysdate,date2001-01-01) from dual; MONTHS_BETWEEN(SYSDATE,DATE2001-01-01) - 34.4035409 SQL 9、next_day()给定一个日期值，返回由第二个参数指出的日子第一次出现在的日期值（应返回相应日子的名称字符串）與周相關日期函數1.查询某周的第一天select trunc(decode(ww, 53, to_date(yy | 3112, yyyyddmm), to_date(yy | - | to_char(ww * 7), yyyy-ddd), d) last_dayfrom (select substr(2004-32, 1, 4) yy, to_number(substr(2004-32, 6) ww from dual)select trunc(to_date(substr(2003-01,1,5)|to_char(to_number(substr(2003-01,6)*7),yyyy-ddd),d)-6 first_day from dualselect min(v_date) from(select (to_date(200201,yyyymm) + rownum) v_datefrom all_tableswhere rownum 370)where to_char(v_date,yyyy-iw) = 2002-492.查询某周的最后一天select trunc(decode(ww, 53, to_date(yy | 3112, yyyyddmm), to_date(yy | - | to_char(ww * 7), yyyy-ddd), d) - 6 first_dayfrom (select substr(2004-33, 1, 4) yy, to_number(substr(2004-33, 6) ww from dual) select trunc(to_date(substr(2003-01,1,5)|to_char(to_number(substr(2003-01,6)*7),yyyy-ddd),d) last_day from dualselect max(v_date) from(select (to_date(200408,yyyymm) + rownum) v_datefrom all_tableswhere rownum 370)where to_char(v_date,yyyy-iw) = 2004-333.查询某周的日期select min_date, to_char(min_date,day) day from(select to_date(substr(2004-33,1,4)|001+rownum-1,yyyyddd) min_date from all_tableswhere rownum = decode(mod(to_number(substr(2004-33,1,4),4),0,366,365)unionselect to_date(substr(2004-33,1,4)-1| decode(mod(to_number(substr(2004-33,1,4)-1,4),0,359,358)+rownum,yyyyddd) min_d