Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric

618517Jan 21 2008 — edited Jan 21 2008
hi

i am trying to create a view to query holiday dates within my database. It is suppossed to show all the dates within the following month, the code is as follows :


CREATE VIEW HolsInNextMonth AS
SELECT Site_Details.site_code,
Site_Details.name,
Holiday_Details.hol_code,
Holiday_Details.description,
Departure_Dates.Departure_Date,
TRUNC(ADD_MONTHS(SYSDATE, 1), 'MONTH') AS next_month_start,
LAST_DAY(ADD_MONTHS(SYSDATE, 1)) AS next_month_end
FROM Site_Details, Holiday_Details, Departure_Dates
WHERE Departure_Dates.Departure_Date BETWEEN to_date('next_month_start', 'dd-mon-yyyy')
AND to_date('next_month_end', 'dd-mon-yyyy')

Ive set up 2 fields within the view to create the start and end dates for the month after the system date. I then want to use this with the between statement to show all the holidays within these dates but i get the ORA-01858: a non-numeric character was found where a numeric was expected error.

Can someone tell me where i am going wrong and please help!

Thanks in advance.

Comments

Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Feb 18 2008
Added on Jan 21 2008
4 comments
567 views