Copy Code code as follows:
-Oracle takes the SQL from Monday to the weekend
This takes the first days of the week, starting with Sunday.
Select To_char (to_date (' 20130906 ', ' yyyymmdd '), ' d ') from dual;
--Result: 6 Note: 2013.09.06 is Friday, the sixth day of the week
Select To_char (sysdate+ (2-to_char (sysdate, ' d '))-7, ' YYYYMMDD ') from dual;---Monday
Select To_char (sysdate+ (2-to_char (sysdate, ' d '))-1, ' YYYYMMDD ') from dual;---Sunday
--a simpler formulation that returns the date type
Select Trunc (sysdate, ' IW ')-7 from dual;---Monday
Select Trunc (sysdate, ' IW ')-1 from dual;--on Sunday
This is the Monday.
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;--on Monday
Select To_char (trunc (sysdate, ' IW ')-1, ' YYYYMMDD ') from dual;--on Sunday
--Get the function of the 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;
--Get the function of the 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 (' 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;
--Search results: 2013/9/6 9:45:14
