本文主要是介绍oracle日期函数及相关查询日期所用的函数，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
1、查询本周一和本周日
select to_date(next_day(sysdate-7,2)) 周一, to_date(next_day(sysdate,1)) 周日 from dual;2、查询 去年的今天
SELECT ADD_MONTHS(to_date(&mytime,'yyyymmdd'), -12) AS "当前时间减一年" FROM dual;3、相关日期函数
--months_between(date1,date2) 返回date1和date2 之间 间隔多少个月
select months_between(sysdate, to_date('20140202','yyyymmdd')) from dual --1.31695191158901--add_months(date,number) 指定的日期上增加number 个月
select add_months(sysdate,1) from dual --2014-4-11 19:42:33--last_day(date) 返回日期date 所在月的最后一天select last_day(sysdate) from dual --2014-3-31 19:44:31-- next_day(day,'day') 下一个星期一 日
select sysdate from dual --当前日期 2014-3-11 19:28:05select to_char(current_timestamp,'yyyymmdd hh24:mi:ss') from dual -- 11-3月 -14 07.28.32.150101 下午 +08:00select next_day(sysdate,'星期二') from dual --2014-3-18 19:27:16 今天就是星期二select next_day(sysdate,'星期日') from dual-- 2014-3-16 19:27:37--extract(c1 from d1) 从日期d1中抽取c1指定的年月日时分秒。
select extract(year from date'2011-05-17') year from dual; --2011select extract(month from date'2011-05-17') month from dual; --5select extract(day from date'2011-05-17') day from dual; --17-- 获取两个日期之间的具体时间间隔，extract函数是最好的选择 -- 时间间隔的情况下 只能 算到天 无法再 加到月上。
select extract(day from dt2-dt1) day ,extract(hour from dt2-dt1) hour ,extract(minute from dt2-dt1) minute ,extract(second from dt2-dt1) second
from ( select to_timestamp('2011-02-04 15:07:00','yyyy-mm-dd hh24:mi:ss') dt1 ,to_timestamp('2011-05-17 19:08:46','yyyy-mm-dd hh24:mi:ss') dt2 from dual) select extract(year from systimestamp) year ,extract(month from systimestamp) month ,extract(day from systimestamp) day ,extract(minute from systimestamp) minute ,extract(second from systimestamp) second ,extract(timezone_hour from systimestamp) th ,extract(timezone_minute from systimestamp) tm ,extract(timezone_region from systimestamp) tr ,extract(timezone_abbr from systimestamp) ta
from dual
http://oracle.chinaitlab.com/induction/817947.html
加法select sysdate,add_months(sysdate,12) from dual; --加1年select sysdate,add_months(sysdate,1) from dual; --加1月select sysdate,to_char(sysdate+7,'yyyy-mm-dd HH24:MI:SS') from dual; --加1星期select sysdate,to_char(sysdate+1,'yyyy-mm-dd HH24:MI:SS') from dual; --加1天select sysdate,to_char(sysdate+1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --加1小时select sysdate,to_char(sysdate+1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1分钟select sysdate,to_char(sysdate+1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --加1秒减法select sysdate,add_months(sysdate,-12) from dual; --减1年select sysdate,add_months(sysdate,-1) from dual; --减1月select sysdate,to_char(sysdate-7,'yyyy-mm-dd HH24:MI:SS') from dual; --减1星期select sysdate,to_char(sysdate-1,'yyyy-mm-dd HH24:MI:SS') from dual; --减1天select sysdate,to_char(sysdate-1/24,'yyyy-mm-dd HH24:MI:SS') from dual; --减1小时select sysdate,to_char(sysdate-1/24/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1分钟select sysdate,to_char(sysdate-1/24/60/60,'yyyy-mm-dd HH24:MI:SS') from dual; --减1秒Oracle关于时间/日期的操作1.日期时间间隔操作当前时间减去7分钟的时间select sysdate,sysdate - interval '7' MINUTE from dual当前时间减去7小时的时间select sysdate - interval '7' hour from dual当前时间减去7天的时间select sysdate - interval '7' day from dual当前时间减去7月的时间select sysdate,sysdate - interval '7' month from dual当前时间减去7年的时间select sysdate,sysdate - interval '7' year from dual时间间隔乘以一个数字select sysdate,sysdate - 8 *interval '2' hour from dual2.日期到字符操作select sysdate,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dualselect sysdate,to_char(sysdate,'yyyy-mm-dd hh:mi:ss') from dualselect sysdate,to_char(sysdate,'yyyy-ddd hh:mi:ss') from dualselect sysdate,to_char(sysdate,'yyyy-mm iw-d hh:mi:ss') from dual参考oracle的相关关文档(ORACLE901DOC/SERVER.901/A90125/SQL_ELEMENTS4.HTM#48515)3. 字符到日期操作select to_date('2003-10-17 21:15:37','yyyy-mm-dd hh24:mi:ss') from dual具体用法和上面的to_char差不多。4. trunk/ ROUND函数的使用select trunc(sysdate ,'YEAR') from dualselect trunc(sysdate ) from dualselect to_char(trunc(sysdate ,'YYYY'),'YYYY') from dual5.oracle有毫秒级的数据类型--返回当前时间 年月日小时分秒毫秒select to_char(current_timestamp(5),'DD-MON-YYYY HH24:MI:SSxFF') from dual;--返回当前 时间的秒毫秒，可以指定秒后面的精度(最大=9)select to_char(current_timestamp(9),'MI:SSxFF') from dual;6.计算程序运行的时间(ms)declaretype rc is ref cursor;l_rc rc;l_dummy all_objects.object_name%type;l_start number default dbms_utility.get_time;beginfor I in 1 .. 1000loopopen l_rc for'select object_name from all_objects '||'where object_id = ' || i;fetch l_rc into l_dummy;这篇关于oracle日期函数及相关查询日期所用的函数的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
