add_months

[aph]

Hi,

I have requirement to check one month back of sysdate to see if hiredate is less then the sysdate. In this where clause I have to use format MON-YYYY on both side client does not want to check with DD-MON-YYYY. How I can check this only month & year? Please see below e.g


where to_char(trunc(hire_date),'MON-YYYY') < add_months(trunc(sysdate),-1)

* The left side of the condition is fine, but don't know how to use MON-YYYY on the right side.

Thanks in advance.

[slimdave]

If what you want is all records where the hire date is more than one month old, then ...

where hire_date < add_months(trunc(sysdate),-1)

Otherwise, please explain more clearly what the requirement is.

[tamilselvan]

 1  select sysdate, to_char(add_months(sysdate,-1),'DD-MON-YYYY') "Previous Month"
  2*  from dual
  3  /

SYSDATE              Previous Mo
-------------------- -----------
02-FEB-2005 12:52:11 02-JAN-2005 

Tamil

[aph]

Hi tamilselvan,

1 select sysdate, to_char(add_months(sysdate,-1),'DD-MON-YYYY') "Previous Month"
2* from dual
3 /

SYSDATE Previous Mo
-------------------- -----------
02-FEB-2005 12:52:11 02-JAN-2005

--------------------------------------------------------------------------------

The problem is we do not want to use DD-MON-YYYY, however need to check by MON-YYYY.


Thanks

[slimdave]

Originally posted by aph
The problem is we do not want to use DD-MON-YYYY, however need to check by MON-YYYY.

When you do this you are comparing two character strings together, and the result it dependent upon your language configuration.

So in English, "FEB-2006" will sort lower than "JAN-2001", therefore ("FEB-2006" < "JAN-2001") is true. So don't use TO_CHAR() when what you really want to do is TRUNC().

[aph]

Ok!

let's say we are in the month of FEB-2005 regardless of DD, and I want to go one month back and see if previous month is JAN-2005.

In where clause for some reason we can not use or do not want to use DD-MON-YYYY from the sysdate. just want to check with the above format.

Thanks

[tamilselvan]

 1  select sysdate, to_char(add_months(sysdate,-1),'MON-YYYY') "Previous Month"
  2*   from dual
15:02:03 SQL> /

SYSDATE   Previous
--------- --------
02-FEB-05 JAN-2005 

Tamil

[gandolf989]

Originally posted by aph
Ok!

let's say we are in the month of FEB-2005 regardless of DD, and I want to go one month back and see if previous month is JAN-2005.

In where clause for some reason we can not use or do not want to use DD-MON-YYYY from the sysdate. just want to check with the above format.

Thanks

Is this what you were looking for?

  1  select trunc(sysdate, 'MM') "current month", 
  2*        add_months(trunc(sysdate, 'MM'), -1) "Previous Month"
  3* from dual
SQL > /

current m Previous
--------- ---------
01-FEB-05 01-JAN-05

Elapsed: 00:00:00.31

[gandolf989]

Originally posted by tamilselvan

 1  select sysdate, to_char(add_months(sysdate,-1),'MON-YYYY') "Previous Month"
  2*   from dual
15:02:03 SQL> /

SYSDATE   Previous
--------- --------
02-FEB-05 JAN-2005 

Tamil

I guess it really doesn't matter what day of the month it is when you do a add_months.

[see_one]

Us this to get the previous month.

select add_months(last_day(add_months(sysdate,-1))+1, -1) Previous_month from dual