Oracle Date Operations

1, get the last day of the month

SELECT To_char (Last_day (to_date (' 20140125 ', ' YYYYMMDD ')), ' YYYYMMDD ') Lastday from  DUAL

2, get the last day of the end of last month:

Select To_char (Add_months (Last_day (' 2008-08-08 ', ' yyyy-mm-dd '), -1), ' Yyyy-mm-dd ') Lastday from dual;

3, Get next monthSelect Add_months (sysdate,1) from dualSelect Last_day (sysdate) +1 from dual4, get the same day last month

SELECT trunc (trunc (sysdate, ' mm ')-1, ' mm ') +to_char (sysdate, ' DD ')-1 from dual;   Select Add_months (to_date (' 20140302 ', ' YYYYMMDD '),-1) from dual

5, get the same day last year

  SELECT  To_char (add_months (to_date (' 201404 ', ' yyyymm '), -12),                         ' Yyyymm ') from dual;

Get Last Sunday

SELECT To_char (Next_day (to_date (' 2014-02-10 ', ' yyyy-mm-dd '), ' Sunday ')-7, ' YYYYMMDD ') from  Dual;oracle gets the week count   Oracle Acquires Week Select TO_CHAR (to_date (' 20141117 ', ' yyyymmdd '), ' ww ') from DUAL;//WW using Oracle's periodic algorithm select To_char (' To_date (' 20141117 ', ' yyyymmdd '), ' IW ') from DUAL;//IW using the standard period algorithm

6, persistent SQL

Select To_date (' 2009-8 ', ' yyyy-mm ') + ROWNUM-1 from  dualconnect by ROWNUM <           (select TRUNC (Last_day (' 2009-8 ', ' yyyy-mm '))-                   TRUNC (add_months (Last_day (' to_date ', ' 2009-8 '),                                    -1)) from              yyyy-mm)

7, Month day

Select To_char (sysdate, ' YYYY "Years" MM "month" DD "Day") from Dual;--select To_char (to_date (' 2014-02-16 ', ' yyyy-mm-dd ') + (2-to_ char (to_date (' 2014-02-16 ', ' yyyy-mm-dd '), ' d '))-1, ' YYYYMMDD ') from dual;

---Oracle takes SQL from Monday to weekends
-This takes the first day of the week, starting in Sunday
Select To_char (To_date (' 20130906 ',' YYYYMMDD '),' d ')  from Dual
--Results: 6 Note: 2013.09.06 is Friday, sixth day of the week

Select To_char (sysdate+(2-To_char (Sysdate,' d '))-7,' YYYYMMDD ')  from Dual---on Monday
Select To_char (sysdate+(2-To_char (Sysdate,' d '))-1,' YYYYMMDD ')  from Dual---on Sunday

--a simpler notation that returns the date type
Select Trunc (Sysdate,' IW ') - 7  from Dual---on Monday
Select Trunc (Sysdate,' IW ') - 1  from Dual--Last Sunday

--This is the Monday.
Select Trunc (Sysdate,' IW ')  from Dual

Select Trunc (To_date (' 20130915 ',' YYYYMMDD '),' IW ')  from Dual
--Results: 2013/9/9 notes: 20130915 for Sunday

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

--Get the function on the Monday
Create  or Replace  function Fun_acc_getlastweekstart (systemdate  inch  Date)
  return  varchar2  is
Result_str  varchar2( the);
begin
  Select To_char (Trunc (Systemdate, ' IW ') - 7, ' YYYYMMDD ')
  into Result_str
  from Dual
  return RESULT_STR;
End Fun_acc_getlastweekstart;

--Get the function on the Sunday
Create  or Replace  function Fun_acc_getlastweekend (systemdate  inch  Date)  return  varchar2  is
Result_str  varchar2( the);
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

Oracle Date Operations