4 Replies Latest reply on Oct 24, 2013 2:43 PM by Sven W.

# Get last august month from current date

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

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

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

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
12      WHEN (dt < Add_Months(TRUNC(dt,'YEAR'),7)) THEN
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

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