1、代码如下:
-- Created on 2020/10/28 by EDZ
declare
-- Local variables here
i integer;
START_TIME DATE:=TO_DATE( '2020-08-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
END_TIME DATE:=TO_DATE( '2020-10-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss');
START_TIME_STR VARCHAR2(20);
END_TIME_STR VARCHAR2(20);
begin
-- Test statements here
while START_TIME < END_TIME loop
START_TIME_STR :=to_char(START_TIME,'yyyy-mm-dd')||' 00:00:00';
END_TIME_STR := to_char(START_TIME,'yyyy-mm-dd')||' 23:59:59';
DBMS_OUTPUT.PUT_LINE(START_TIME_STR||' '||END_TIME_STR);
--DBMS_OUTPUT.PUT_LINE(to_date(START_TIME_STR,'yyyy-mm-dd hh24:mi:ss')||' '||to_date(END_TIME_STR,'yyyy-mm-dd hh24:mi:ss'));
START_TIME:= START_TIME + 1;
end loop;
end;
执行结果如下图所示:
2、通过当前月获得下一个月的日期:
ADD_MONTHS(TO_DATE(V_MAX_MONTH,'yyyymm'), 1)
3、当前日期的年份第一天和最后一天
(1)第一天
select trunc(sysdate,'y') FROM DUAL;
select trunc(sysdate,'yy') FROM DUAL;
select trunc(sysdate,'yyy') FROM DUAL;
select trunc(sysdate,'yyyy') FROM DUAL;
(2)最后一天
select last_day(add_months(trunc(SYSDATE,'y'),11)) from dual
4、当前日期的月份第一天和最后一天
(1)第一天
select trunc(sysdate,'MM') from dual;
返回的是当前月的第一天的00:00:00
(2)最后天
select last_day(sysdate) from dual;
返回当前月的最后一天的00:00:00
5、得到当前月的第一天凌晨和最后一天的23:59:59秒
-- Created on 2020/12/8 by EDZ
declare
-- Local variables here
i integer;
V_S_DATE DATE;
START_DATE DATE;
END_DATE DATE;
begin
-- Test statements here
V_S_DATE:=TO_DATE('202001','yyyymm');
select trunc(V_S_DATE,'MM') INTO START_DATE from dual;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(START_DATE,'YYYY-MM-DD HH24:MI:SS'));
select last_day(V_S_DATE) INTO END_DATE from dual;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(END_DATE,'YYYY-MM-DD')||' 23:59:59');
end;
如下图所示:
5、将字符串27-11月-20转换成date类型,如:
select to_char(cast(cast('27-11月-20' as timestamp) as date),'yyyy-mm-dd hh24:mi:ss') dateStr from dual;
输出结果如下图所示: