oracle Obtains the start time and end time of the last ten days

  • 2021-09-25 00:02:46
  • OfStack

 
--  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 

Related articles: