-- Oracle Take last week 1 By the weekend sql
-- What is taken in this way is In 1 What day of the week begins with Sunday
select to_char(to_date('20130906','yyyymmdd'),'d') from dual;
-- Results: 6 Note: 2013.09.06 It's a week 5 For this week's first 6 Days
select to_char(sysdate+(2-to_char(sysdate,'d'))-7,'yyyymmdd') from dual;--- Last week 1
select to_char(sysdate+(2-to_char(sysdate,'d'))-1,'yyyymmdd') from dual;--- Last Sunday
-- 1 A simpler way to write it , Return date Type
select trunc(sysdate,'iw') - 7 from dual;--- Last week 1
select trunc(sysdate,'iw') - 1 from dual;-- Last Sunday
-- So it's found out this week 1
select trunc(sysdate,'iw') from dual;
select trunc(to_date('20130915','yyyymmdd'),'iw') from dual;
-- Results: 2013/9/9 Note: 20130915 For Sunday
-- Return char Type
select to_char(trunc(sysdate,'iw') - 7,'yyyymmdd') from dual;-- Last week 1
select to_char(trunc(sysdate,'iw') - 1,'yyyymmdd') from dual;-- Last Sunday
-- Get last week 1 Function of
create or replace function fun_acc_getlastweekstart(systemdate in date)
return varchar2 is
result_str varchar2(15);
begin
select to_char(trunc(systemdate, 'iw') - 7, 'yyyymmdd')
into result_str
from dual;
return result_str;
end fun_acc_getlastweekstart;
-- Get last Sunday's function
create or replace function fun_acc_getlastweekend(systemdate in date) return varchar2 is
result_str varchar2(15);
begin
select to_char(trunc(systemdate, 'iw') - 1, 'yyyymmdd')
into result_str
from dual;
return result_str;
end fun_acc_getlastweekend;
-- Test this function
select fun_acc_getlastweekstart(sysdate) from dual;
select fun_acc_getlastweekend(sysdate) from dual;
select fun_acc_getlastweekstart(to_date('20130915','yyyymmdd')) from dual;
select fun_acc_getlastweekend(to_date('20130915','yyyymmdd')) from dual;
-- Query results: 20130826 , 20130901 , 20130902 , 20130908
-- Note:
select sysdate from dual;
-- Query results: 2013/9/6 9:45:14