4 Replies Latest reply on Jan 21, 2008 7:03 PM by 618517

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


      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,
      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.