Oracle date operations, oracle date

Oracle date operations, oracle date
1. Obtain the last day of a month.

SELECT TO_CHAR(LAST_DAY(TO_DATE('20140125', 'yyyymmdd')), 'yyyymmdd') LASTDAY  FROM DUAL

2. Obtain the last day of the last month:

select to_char(add_months(last_day(to_date('2008-08-08','yyyy-MM-dd')),-1),'yyyy-MM-dd') LastDay from dual;

3. Obtain the next month select add_months (sysdate, 1) from dualselect last_day (sysdate) + 1 from dual4, get the same day of the previous 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. obtain the same day of last year

  SELECT  TO_CHAR(ADD_MONTHS(TO_DATE('201404', 'yyyymm'), -12),                         'yyyymm') FROM dual;

Retrieve last Sunday

SELECT TO_CHAR (NEXT_DAY (TO_DATE ('1970-02-10 ', 'yyyy-mm-dd'), 'sunday')-7, 'yyyymmdd') from dual; oracle get week count oracle get week select TO_CHAR (to_date ('201312', 'yyyymmdd'), 'ww ') from dual; // ww use the oracle periodic algorithm select TO_CHAR (to_date ('201312', 'yyyymmdd'), 'iw') from dual; // iw uses the standard periodic Algorithm

6. Continuous SQL

 SELECT TO_DATE('2009-8', 'YYYY-MM') + ROWNUM - 1  FROM DUALCONNECT BY ROWNUM <           (SELECT TRUNC(LAST_DAY(TO_DATE('2009-8', 'YYYY-MM'))) -                   TRUNC(ADD_MONTHS(LAST_DAY(TO_DATE('2009-8', 'YYYY-MM')),                                    -1))              FROM DUAL)

7, year, month, day

Select to_char (sysdate, 'yyyy "year" MM "month" DD "day" ') from dual; -- select to_char (to_date ('2017-02-16 ', 'yyyy-mm-dd') + (2-to_char (to_date ('2017-02-16 ', 'yyyy-mm-dd'), 'D')-1, 'yyyymmdd') from dual;

---- Oracle obtains the SQL statement from last Monday to weekend
-- The day of the week starts on Sunday.
SelectTo_char (to_date ('20170101', 'yyyymmdd'), 'D ') FromDual;
-- Result: Note 6: the sixth day of the week is Friday.

SelectTo_char (sysdate + (2-to_char (sysdate, 'D')-7, 'yyyymmdd ') FromDual; --- last Monday
SelectTo_char (sysdate + (2-to_char (sysdate, 'D')-1, 'yyyymmdd ') FromDual; --- last Sunday

-- Return the date type in a simpler way
SelectTrunc (sysdate, 'iw')-7 FromDual; --- last Monday
SelectTrunc (sysdate, 'iw')-1 FromDual; -- last Sunday

-- This is Monday.
SelectTrunc (sysdate, 'iw ') FromDual;

SelectTrunc (to_date ('20140901', 'yyyymmdd'), 'iw ') FromDual;
-- Result: Note: 20130915 is Sunday

-- Returns the char type.
SelectTo_char (trunc (sysdate, 'iw')-7, 'yyyymmdd ') FromDual; -- last Monday
SelectTo_char (trunc (sysdate, 'iw')-1, 'yyyymmdd ') FromDual; -- last Sunday

-- Obtain the function of last Monday
Create  OrReplace FunctionFun_acc_getlastweekstart (systemdate In  Date)
Return  Varchar2  Is
Result_str Varchar2(15 );
Begin
SelectTo_char (trunc (systemdate, 'iw')-7, 'yyyymmdd ')
IntoResult_str
FromDual;
ReturnResult_str;
EndFun_acc_getlastweekstart;

-- Obtain the function of last Sunday
Create  OrReplace FunctionFun_acc_getlastweekend (systemdate In  Date) Return  Varchar2  Is
Result_str Varchar2(15 );
Begin
SelectTo_char (trunc (systemdate, 'iw')-1, 'yyyymmdd ')
IntoResult_str
FromDual;
ReturnResult_str;
EndFun_acc_getlastweekend;

-- Test this function.
SelectFun_acc_getlastweekstart (sysdate) FromDual;
SelectFun_acc_getlastweekend (sysdate) FromDual;
SelectFun_acc_getlastweekstart (to_date ('201312', 'yyyymmdd ')) FromDual;
SelectFun_acc_getlastweekend (to_date ('201312', 'yyyymmdd ')) FromDual;
-- Query results: 20130826, 20130901, 20130902, and 20130908
-- Note:
SelectSysdate FromDual;
-- Query Result: 9:45:14