add_months
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: add_months

  1. #1
    Join Date
    Jul 2001
    Posts
    334

    add_months

    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.

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    If what you want is all records where the hire date is more than one month old, then ...
    Code:
    where hire_date < add_months(trunc(sysdate),-1)
    Otherwise, please explain more clearly what the requirement is.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
     1  select sysdateto_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

  4. #4
    Join Date
    Jul 2001
    Posts
    334
    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

  5. #5
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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().
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  6. #6
    Join Date
    Jul 2001
    Posts
    334
    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

  7. #7
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    PHP Code:
     1  select sysdateto_char(add_months(sysdate,-1),'MON-YYYY'"Previous Month"
      
    2*   from dual
    15
    :02:03 SQL> /

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

  8. #8
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    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?

    Code:
      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
    this space intentionally left blank

  9. #9
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Originally posted by tamilselvan
    PHP Code:
     1  select sysdateto_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.
    this space intentionally left blank

  10. #10
    Join Date
    Jun 2004
    Posts
    125
    Us this to get the previous month.

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width