DATE PROBLEM ORA-01858 - a non-numeric character was found where a numeric
618517Jan 21 2008 — edited Jan 21 2008hi
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.