-- Get the first ten days start time
create or replace function fd_lastxunstart(rq in date) return string is
refstr varchar2(50);
v_rq date;
begin
-- Get on 1 Date of ten days
v_rq := trunc(rq);
select case decode(trunc((to_char(v_rq, 'dd') - 1) / 10),
0,
' Early ten days ',
1,
' Mid-term ',
' Late ten days ')
when ' Early ten days ' then -- Go back to the last half of last month
to_char(add_months(v_rq, -1), 'yyyyMM') || '21'
when ' Mid-term ' then
to_char(v_rq, 'yyyymm') || '01' else
to_char(v_rq, 'yyyymm') || '11'
end
into refstr
from dual;
return refstr;
end fd_lastxunstart;
-- This returns the start date of the first ten days
select sysdate from dual;
select fd_lastxunstart(sysdate) from dual;
select fd_lastxunstart(to_date('20130305','yyyymmdd')) from dual;
select fd_lastxunstart(to_date('20130311','yyyymmdd')) from dual;
select fd_lastxunstart(to_date('20130325','yyyymmdd')) from dual;
-- The execution result is: 2013/9/5 12:08:39 , 20130821 , 20130221 , 20130301 , 20130311
---- Get on 1 The end date of ten days
-- Pass in 1 A date Returns a value of type 1 A varchar The end date of the first ten days of the type
create or replace function fd_lastxunend(rq in date) return string is
refstr varchar2(50);
v_rq date;
begin
-- Get on 1 Date of ten days
v_rq := trunc(rq);
select case decode(trunc((to_char(v_rq, 'dd') - 1) / 10),
0,
' Early ten days ',
1,
' Mid-term ',
' Late ten days ')
when ' Early ten days ' then -- Return to the last of last month 1 Days
--chr(39) This is in quotation marks
to_char(last_day(add_months(v_rq, -1)) + 1 - 1 / 24 / 60 / 60,
'yyyymmdd')
when ' Mid-term ' then
to_char(v_rq, 'yyyymm') || '10' else
to_char(v_rq, 'yyyymm') || '20'
end
into refstr
from dual;
return refstr;
end fd_lastxunend;
-- This gets the end date of the first ten days
select fd_lastxunend(sysdate) from dual;
select fd_lastxunend(to_date('20130305','yyyymmdd')) from dual;
select fd_lastxunend(to_date('20130311','yyyymmdd')) from dual;
select fd_lastxunend(to_date('20130315','yyyymmdd')) from dual;
select fd_lastxunend(to_date('20130221','yyyymmdd')) from dual;
-- Implementation results: 20130831 , 20130228 , 20130310 , 20130310 , 20130220
-- Observation 1 / 24 / 60 / 60 The role of This is 1 Seconds
select last_day(add_months(trunc(sysdate), -1)) + 1 - 1 / 24 / 60 / 60
from dual;
select last_day(add_months(trunc(sysdate), -1)) from dual;
select last_day(add_months(trunc(sysdate), -1)) + 1 from dual;
-- Implementation results: 2013/8/31 23:59:59 , 2013/8/31 , 2013/9/1