4 Replies Latest reply: Oct 24, 2013 9:43 AM by Sven W. RSS

    Get last august month from current date

    skapex

      Hi,

      I need to get last august month from current date.

       

      e.g if current date is 1-OCT-2013 need to get last August date i.e.1-AUG-2013

       

      e.g. if current date is 1-MAY-2013 need to get last August date i.e.1-AUG-2012

        • 1. Re: Get last august month from current date
          chris227

          ah no,misread ;-)

          • 2. Re: Get last august month from current date
            Frank Kulash

            Hi,

             

            Here's one way:

             

            ADD_MONTHS ( TRUNC ( ADD_MONTHS (SYSDATE, -7)   -- or -8.  See note below

                               , 'YEAR'

                               )

                       , 7

                       )

             

            I assume that if you run this in August, 2013, then you want to return August, 2013.  If you want August 2012, then change -7 to -8 above.  Leave the last 7 as it is.

             

            Here's how it works:

            ADD_MONTHS (SYSDATE, -7)      will be a date in the year that you want.  That is, if today is between January and July of 2013, subtracting 7 months will give a date in 2012.  If today is August through Decemeber, 2013, then subtracting 7 months will produce a date in 2013.

            TRUNC (ADD_MONTHS (SYSDATE, -7), 'YEAR')                 will be January 1 of the year that you want, and so

            ADD_MONTHS (TRUNC (ADD_MONTHS (SYSDATE, -7), 'YEAR'), 7) will be August 1 of that year.

            • 3. Re: Get last august month from current date
              ranit B

              Something like this?

               

              SQL> WITH table_x AS(
                2     SELECT SYSDATE dt from dual UNION ALL
                3     SELECT TO_DATE('20-05-2013','dd-mm-yyyy') from dual
                4  )
                5  --
                6  ---
                7  --
                8  SELECT  dt,
                9    CASE
              10      WHEN (dt >= Add_Months(TRUNC(dt,'YEAR'),7)) THEN
              11          Add_Months(TRUNC(dt,'YEAR'),7)
              12      WHEN (dt < Add_Months(TRUNC(dt,'YEAR'),7)) THEN
              13          Add_Months(TRUNC(dt,'YEAR'),7) - 365
              14    END cs
              15  FROM table_x;

              DT        CS
              --------- ---------
              24-OCT-13 01-AUG-13
              20-MAY-13 01-AUG-12

              • 4. Re: Get last august month from current date
                Sven W.

                Well ranitB you should not use 365 as a value for a year. Similiar logic just with less flaws would be:

                 

                select case when sysdate >= add_months(trunc(sysdate,'YEAR'),7)
                       then add_months(trunc(sysdate,'YEAR'), 7) -- this year
                       else add_months(trunc(sysdate,'YEAR'),-5) -- prev year
                       end
                from dual;