Copy codeThe Code is as follows:
-- Oracle reads SQL statements from last Monday to weekend
-- The day of the week starts on Sunday.
Select to_char (to_date ('201312', 'yyyymmdd'), 'D') from dual;
-- Result: Note 6: the sixth day of the week is Friday.
Select to_char (sysdate + (2-to_char (sysdate, 'D')-7, 'yyyymmdd') from dual; --- last Monday
Select to_char (sysdate + (2-to_char (sysdate, 'D')-1, 'yyyymmdd') from dual; --- last Sunday
-- Return the date type in a simpler way
Select trunc (sysdate, 'iw')-7 from dual; --- last Monday
Select trunc (sysdate, 'iw')-1 from dual; -- last Sunday
-- This is Monday.
Select trunc (sysdate, 'iw') from dual;
Select trunc (to_date ('20170101', 'yyyymmdd'), 'aws') from dual;
-- Result: Note: 20130915 is Sunday
-- Returns the char type.
Select to_char (trunc (sysdate, 'iw')-7, 'yyyymmdd') from dual; -- last Monday
Select to_char (trunc (sysdate, 'iw')-1, 'yyyymmdd') from dual; -- last Sunday
-- Obtain the function of last Monday
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;
-- Obtain the function of last Sunday
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 ('201312', 'yyyymmdd') from dual;
Select fun_acc_getlastweekend (to_date ('201312', 'yyyymmdd') from dual;
-- Query results: 20130826, 20130901, 20130902, and 20130908
-- Note:
Select sysdate from dual;
-- Query Result: 9:45:14