Oracle functions for obtaining the start time and end time of the last ten days

Oracle functions for obtaining the start time and end time of the last ten days

[SQL] -- get create or replace function fd_lastxunstart (rq in date) return string is refstr varchar2 (50); v_rq date; begin -- get date v_rq of the last ten: = trunc (rq); select case decode (trunc (to_char (v_rq, 'dd')-1)/10), 0, 'upgrade', 1, 'mid ', 'late then ') when 'late then -- returns to_char (add_months (v_rq,-1), 'yyymm') of the last month ') | '21' when' mid-term 'then to_char (v_rq, 'yyymmm') | '01' else to_char (v_rq, 'yyymmm ') | '11' end into refstr from dual; return refstr; end fd_lastxunstart; -- the returned result is: select sysdate from dual; select fd_lastxunstart (sysdate) from dual; select fd_lastxunstart (to_date ('201312', 'yyyymmdd') from dual; select fd_lastxunstart (to_date ('20170101', 'yyyymmdd') from dual; select fd_lastxunstart (to_date ('201312', 'yyyymmdd') from dual; -- the execution result is: 12:08:39, 20130821, 20130221, 20130301, 20130311, ---- get the end date of the last ten days -- pass in a value of the date type, returns the create or replace function fd_lastxunend (rq in date) return string is refstr varchar2 (50); v_rq date; begin -- Obtain the date v_rq of the previous day: = trunc (rq); select case decode (trunc (to_char (v_rq, 'dd')-1)/10), 0, 'upgrade', 1, 'mid ', 'late then ') when 'late then -- returns the last day of the previous month -- chr (39). This is a quotation mark to_char (last_day (add_months (v_rq,-1 )) + 1-1/24/60/60, 'yyyymmdd') when' mid-term 'then to_char (v_rq, 'yyyymmm') | '10' else to_char (v_rq, 'yyyymmd ') | '20' end into refstr from dual; return refstr; end fd_lastxunend; -- the obtained result is: select fd_lastxunend (sysdate) from dual; select fd_lastxunend (to_date ('201312', 'yyyymmdd') from dual; select fd_lastxunend (to_date ('20170101', 'yyyymmdd') from dual; select fd_lastxunend (to_date ('201312', 'yyyymmdd') from dual; select fd_lastxunend (to_date ('20170101', 'yyyymmdd') from dual; -- execution result: 20130831, 20130228, 20130310, 20130310, 20130220 -- observe the effect of 1/24/60/60. This is a second 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; -- execution result: 23:59:59