--上一月，上一年
select add_months(sysdate,-1) last_month,add_months(sysdate,-12) last_year from dual;
--下一月，下一年
select add_months(sysdate,1) last_month,add_months(sysdate,12) last_year from dual;
--当月最后一天
select last_day(sysdate) from dual;
--下周日期
select next_day(sysdate,'星期五') "下周五" from dual;
select next_day(sysdate, 'friday') "next friday" from dual;
select next_day(sysdate, 4) from dual;
--固定日期一天
select * from account a where a.applytime>= to_date('2011-02-26','yyyy-mm-dd') and a.applytime< to_date('2011-02-27','yyyy-mm-dd');
--前天一天
select * from account a where a.applytime>= to_date(to_char(sysdate-2,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate-1,'yyyy-mm-dd'),'yyyy-mm-dd');
-- 昨天一天
select * from dxw_tmp a where a.applytime>= to_date(to_char(sysdate-1,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd');
--今天一天
select * from account a where a.applytime>= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime< to_date(to_char(sysdate+1,'yyyy-mm-dd'),'yyyy-mm-dd');
-- 上月第一天
select to_date(to_char(add_months(sysdate,-2)+1,'yyyy-mm-dd'),'yyyy-mm-dd') from dual ;
-- 上月最后一天
select to_date(to_char(add_months(sysdate,-1),'yyyy-mm-dd'),'yyyy-mm-dd') from dual;
select * from account a where a.applytime>= to_date(to_char(add_months(sysdate,-2)+1,'yyyy-mm-dd'),'yyyy-mm-dd') and a.applytime<= to_date(to_char(add_months(sysdate,-1),'yyyy-mm-dd'),'yyyy-mm-dd');
select to_date(to_char(add_months(sysdate,-2),'yyyy-mm-dd'),'yyyy-mm-dd') from dual;
select to_date(to_char(add_months(sysdate,-3)+1,'yyyy-mm-dd'),'yyyy-mm-dd') from dual ;
--trunc 截取日期
--当天零点
select trunc(sysdate,'dd') from dual;
--当月一号
select trunc(sysdate,'mm') from dual;
--本年一月一号
select trunc(sysdate,'yyyy') from dual;
--当前毫秒级时间 6位
select to_char(current_timestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;
--字符串转为 timestamp类型
select to_timestamp('2012-02-03 10:29:46.453234','yyyy-mm-dd hh24:mi:ss.ff6') from dual;
--timestamp转为字符型
select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff6') from dual;
select cast(sysdate as timestamp) from dual;
select to_char(sysdate,'yyyymmdd w hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd ww hh24:mi:ss') from dual;
select to_char(sysdate,'yyyymmdd ww hh24:mi:ss') from dual;
select to_char(systimestamp,'yyyymmdd ww hh24:mi:ss.ff6') from dual;
-- 月第几周
select to_char(sysdate,'yyyy-mm-dd w hh24:mi:ss') from dual;
-- 年第几周
select to_char(sysdate,'yyyy-mm-dd ww hh24:mi:ss') from dual;
-- 第几季度 ff6 date 没有6位的秒位
select to_char(sysdate,'yyyy-mm-dd q ww hh24:mi:ss') from dual;
select to_char(systimestamp,'yyyy-mm-dd q ww hh24:mi:ss.ff6') from dual;
select to_char(sysdate,'sssss') from dual;
/*
o_date格式(以时间:2007-11-02 13:45:25为例)
year:
yy two digits 两位年 显示值:07
yyy three digits 三位年 显示值:007
yyyy four digits 四位年 显示值:2007
month:
mm number 两位月 显示值:11
mon abbreviated 字符集表示 显示值:11月,若是英文版,显示nov
month spelled out 字符集表示 显示值:11月,若是英文版,显示november
day:
dd number 当月第几天 显示值:02
ddd number 当年第几天 显示值:02
dy abbreviated 当周第几天简写 显示值:星期五,若是英文版,显示fri
day spelled out 当周第几天全写 显示值:星期五,若是英文版,显示friday
ddspth spelled out, ordinal twelfth
hour:
hh two digits 12小时进制 显示值:01
hh24 two digits 24小时进制 显示值:13
minute:
mi two digits 60进制 显示值:45
second:
ss two digits 60进制 显示值:25
其它
q digit 季度 显示值:4
ww digit 当年第几周 显示值:44
w digit 当月第几周 显示值:1
24小时格式下时间范围为： 0:00:00 - 23:59:59....
12小时格式下时间范围为： 1:00:00 - 12:59:59 ....
*/
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))/365) as spanyears from dual --时间差-年
select ceil(months_between(sysdate,to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spanmonths from dual --时间差-月
select months_between('19-12月-1999','19-3月-1999') mon_between from dual;
select months_between(to_date('2000.05.20','yyyy.mm.dd'),to_date('2005.05.20','yyyy.mm.dd')) mon_betw from dual;
select (extract(year from to_date('2009-05-01','yyyy-mm-dd')) - extract(year from to_date('2008-04-30','yyyy-mm-dd'))) * 12 +
extract(month from to_date('2008-05-01','yyyy-mm-dd')) - extract(month from to_date('2008-04-30','yyyy-mm-dd')) months
from dual;
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))) as spandays from dual --时间差-天
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24) as spanhours from dual --时间差-时
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60) as spanminutes from dual --时间差-分
select floor(to_number(sysdate-to_date('2007-11-02 15:55:03','yyyy-mm-dd hh24:mi:ss'))*24*60*60) as spanseconds from dual --时间差-秒
select ceil((to_date('2008-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - to_date('2008-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60 * 1000) 相差豪秒数 from dual;
select ceil((to_date('2018-05-02 00:00:00' , 'yyyy-mm-dd hh24-mi-ss') - to_date('2018-04-30 23:59:59' , 'yyyy-mm-dd hh24-mi-ss')) * 24 * 60 * 60) 相差秒数 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;
--加7天
select sysdate+7 from dual;
-- https://docs.oracle.com/cd/e11882_01/server.112/e10729/ch4datetime.htm#nlspg256
declare
ddate timestamp;
sdate date;
begin
ddate:= systimestamp;
sdate:=sysdate;
dbms_output.put_line(ddate);
dbms_output.put_line(sdate);
end;
