1〉获得若干分钟前的时间 select sysdate,sysdate - N/(60*24) from dual; 或者 select sysdate,sysdate - interval 'N' minute from dual
2〉获得若干分钟后的时间 select sysdate,sysdate + N/(60*24) from dual; 或者 select sysdate,sysdate + interval 'N' minute from dual
注释："N"表示需要减去或者加上的分钟数
3〉获得若干秒前的时间 select sysdate,sysdate - N*0.00001 from dual; 或者 select sysdate,sysdate - N/(60*60*24) from dual; 或者 select sysdate,sysdate - interval 'N' second from dual
4〉获得若干秒后的时间 select sysdate,sysdate + N*0.00001 from dual; 或者 select sysdate,sysdate - N/(60*60*24) from dual; 或者 select sysdate,sysdate + interval 'N' second from dual
注释："N"表示需要减去或者加上的秒数 注释：N*0.00001 等价于 N/(60*60*24)
5〉获得若干小时前的时间 select sysdate,sysdate - N*/24 from dual; 或者 select sysdate,sysdate - interval 'N' hour from dual
6〉获得若干小时后的时间 select sysdate,sysdate + N*/24 from dual; 或者 select sysdate,sysdate + interval 'N' hour from dual
注释："N"表示需要减去或者加上的小时数
7〉获得若干月之后同一时间 select sysdate,add_months(sysdate,N) from dual; 或者 select sysdate,sysdate + interval 'N' month from dual
8〉获得若干月之前的同一时间 select sysdate,add_months(sysdate,-N) from dual; 或者 select sysdate,sysdate - interval 'N' month from dual
注释："N"表示需要减去或者加上的月数
9〉获得某个日期所在月份的第一天 select sysdate,trunc(sysdate,'mm') from dual;
10〉获得某个日期所在年的第一天 select sysdate,trunc(sysdate,'yyyy') from dual; 或者 select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy')) * 100 + 1) * 100 + 1),'yyyymmdd') from dual
11〉获得某个日期所在年的最后一天 select sysdate,to_date((to_char(sysdate,'yyyy') || '1231'),'yyyymmdd') from dual; 或者 select sysdate,to_date(to_char((to_number(to_char(sysdate,'yyyy'))* 100 + 12) * 100 + 31),'yyyymmdd') from dual
12〉获得某个日期所在月份的最后一天 select sysdate,last_day(sysdate) from dual; 13〉获得两个日期之间的时数 select t.intime,t.outtime,trunc(24*(t.outtime-t.intime)) from hr_carding t
14〉获得两个日期之间的天数 select t.begintime,t.endtime,trunc(t.endtime-t.begintime) from hr_absence t
15〉获得若干天前的时间 select sysdate,sysdate - N from dual; 或者 select sysdate,sysdate - interval 'N' day from dual
16〉获得若干天后的时间 select sysdate,sysdate + N from dual; 或者 select sysdate,sysdate + interval 'N' day from dual 注释："N"表示需要减去或者加上的天数
17〉获得若干年前的时间 select sysdate,add_months(sysdate,-12*N) from dual; 或者 select sysdate,sysdate - interval 'N' year from dual
16〉获得若干年后的时间 select sysdate,add_months(sysdate,12*N) from dual; 或者 select sysdate,sysdate + interval 'N' year from dual 注释："N"表示需要减去或者加上的年数
17〉相隔若干小时的时间 select sysdate,sysdate - M * interval 'N' hour from dual
18〉检索出某一个日期所在月份的所有天信息
select trunc(to_date('X','yyyymmdd'),'mm') from dual union all select trunc(to_date('X','yyyymmdd'),'mm') + rownum from dual connect by rownum <= (last_day(to_date('X','yyyymmdd')) - trunc(to_date('X','yyyymmdd'),'mm'))
或者 select first + rownum - 1 myday from (select trunc(to_date('X','yyyymmdd'),'MM') first,trunc(last_day(to_date('X','yyyymmdd'))) last from dual) connect by rownum <= last - first + 1
其中：字符串'X'表示某一个日期
19〉怎样知道今天是星期几
select to_char(sysdate,'day') from dual;
在获取之前可以设置日期语言,如: ALTER SESSION SET NLS_DATE_LANGUAGE='AMERICAN'; 还可以在函数中指定 select to_char(to_date('2002-08-26','yyyy-mm-dd'),'day','NLS_DATE_LANGUAGE = AMERICAN') from dual; 其它更多用法,可以参考to_char与to_date函数
20〉本月的天数 SELECT to_char(last_day(SYSDATE),'dd') days FROM dual
21〉今年的天数 select add_months(trunc(sysdate, 'year'), 12) - trunc(sysdate, 'year') from dual
22〉下个星期一的日期
SELECT Next_day(SYSDATE,'monday') FROM dual
23〉怎么样从数据库中获得毫秒 9i以上版本，有一个timestamp类型获得毫秒，如 select to_char(systimestamp ,'yyyy-mm-dd hh24:mi:ssxff') time1, to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ssxff') time2 from dual;
24〉检索某一年度的日历表（转载）：
select case when (new_yweek = min(new_yweek) over(partition by mon order by new_yweek)) then mon else null end as mon, new_yweek as yweek, row_number() over(partition by mon order by new_yweek) as mweek, sum(decode(wday, '1', mday, null)) as sunday, sum(decode(wday, '2', mday, null)) as monday, sum(decode(wday, '3', mday, null)) as tuesday, sum(decode(wday, '4', mday, null)) as wednesday, sum(decode(wday, '5', mday, null)) as thursday, sum(decode(wday, '6', mday, null)) as friday, sum(decode(wday, '7', mday, null)) as saturday, &year as year from (select to_date(&year || '0101', 'yyyymmdd') + rownum - 1 as everyday, to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'mm') as mon, to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'w') as mweek, to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'ww') as yweek, case when (to_char(to_date(&year || '0101', 'yyyymmdd'), 'd') > '1') and (to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'd') < to_char(to_date(&year || '0101', 'yyyymmdd'), 'd')) then to_char(to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'ww') + 1, 'fm00') else to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'ww') end as new_yweek, to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'd') as wday, to_char(to_date(&year || '0101', 'yyyymmdd') + rownum - 1, 'dd') as mday from (select rownum r from dual connect by rownum <= 366) where rownum <= to_char(to_date(&year || '1231', 'yyyymmdd'), 'ddd')) group by mon, new_yweek
转载于:https://www.cnblogs.com/liuzhuqing/archive/2012/06/29/7480835.html
