Getting Error – ORA-01858: a non-numeric character was found where a numeric was expected

by

The error you are getting is either because you are doing TO_DATE on a column that’s already a date, and you’re using a format mask that is different to your nls_date_format parameter[1] or because the event_occurrence column contains data that isn’t a number.

You need to a) correct your query so that it’s not using TO_DATE on the date column, and b) correct your data, if event_occurrence is supposed to be just numbers.

And fix the datatype of that column to make sure you can only store numbers.

[1] What Oracle does when you do: TO_DATE(date_column, non_default_format_mask) is:
TO_DATE(TO_CHAR(date_column, nls_date_format), non_default_format_mask)

Generally, the default nls_date_format parameter is set to dd-MON-yy, so in your query, what is likely to be happening is your date column is converted to a string in the format dd-MON-yy, and you’re then turning it back to a date using the format MMDD. The string is not in this format, so you get an error.

More Related Contents:

  1. ANYONE CAN TELL ME THAT WHY THIS CODE IS GIVING ME AN ERROR OF TABLE OT CLUSTER KEY WORD IS MISSING? [closed]
  2. How do I escape a reserved word in Oracle?
  3. How to put more than 1000 values into an Oracle IN clause [duplicate]
  4. ORA-30926: unable to get a stable set of rows in the source tables
  5. ORA-12514 TNS:listener does not currently know of service requested in connect descriptor
  6. Fetch Oracle table type from stored procedure using JDBC
  7. “Safe” TO_NUMBER()
  8. When or Why to use a “SET DEFINE OFF” in Oracle Database
  9. error: ORA-65096: invalid common user or role name in oracle
  10. Oracle insert if not exists statement
  11. Error : ORA-01704: string literal too long
  12. Using the “IN” clause with a comma delimited string from the output of a replace() function in Oracle SQL
  13. Oracle client ORA-12541: TNS:no listener [closed]
  14. Reason why oracle is case sensitive?
  15. Create an Excel Spreadsheet from a Oracle Database
  16. How do I turn off Oracle password expiration?
  17. ORACLE IIF Statement
  18. Table-Valued Functions in ORACLE 11g ? ( parameterized views )
  19. SQL Fiddle Output Error
  20. oracle PL/SQL how to calculate range ip for IPv6 cidr
  21. How to increase performance for bulk INSERTs to ODBC linked tables in Access?
  22. Oracle Age calculation from Date of birth and Today
  23. Trying to export a Oracle via PL/SQL gives a date of 0000-00-00
  24. How to list active / open connections in Oracle?
  25. ORA-12705: Cannot access NLS data files or invalid environment
  26. jboss 7 oracle datasource configuration
  27. How to see refcursor result/output in Oracle SQL Developer? [duplicate]
  28. How do I get column datatype in Oracle with PL-SQL with low privileges?
  29. Query to search all packages for table and/or column
  30. How to View Oracle Stored Procedure using SQLPlus?

Leave a Comment