Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003
    Location
    England
    Posts
    26

    Unanswered: add_months function

    Hi - I am running an SQL on my oracle database.

    I have used the add_months function in my select statement and that is all working fine.

    I now want a parameter to show me only rows where the result of the add_months function is 1st July.

    I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

    SQL is as follows

    select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
    from accounts
    where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
    ;

    Any advise would be appreciated.
    Regards,
    Beth
    Beth

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: add_months function

    Originally posted by elisabeth
    Hi - I am running an SQL on my oracle database.

    I have used the add_months function in my select statement and that is all working fine.

    I now want a parameter to show me only rows where the result of the add_months function is 1st July.

    I am getting an error message "ORA-01841: (full) year must be between -4713 and +9999, and not be 0". Does anyone know the exact format of how I should enter my date? I have tried a few different ways.

    SQL is as follows

    select acc_account_no,acc_term_band_start_date,acc_term_i nterval,add_months(acc_term_band_start_date,(acc_t erm_interval*12))
    from accounts
    where add_months(acc_term_band_start_date,(acc_term_inte rval*12) = '01-JUL-2003'
    ;

    Any advise would be appreciated.
    Regards,
    Beth
    Never use a character string literal like '01-JUL-2003' where a DATE is required. Instead, use TO_DATE with an explicit format mask to properly convert it to a date:

    TO_DATE('01-JUL-2003','DD-MON-YYYY')

Posting Permissions

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