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,
BethOriginally 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')