20

I am getting the error in the below sql:

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

SELECT   c.contract_num,
         CASE
            WHEN   (  MAX (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                    - MIN (TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                 / COUNT (c.event_occurrence) < 32
            THEN
              'Monthly'
            WHEN       (  MAX (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                        - MIN (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                     / COUNT (c.event_occurrence) >= 32
                 AND   (  MAX (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD'))
                        - MIN (
                            TO_CHAR (TO_DATE (c.event_dt, 'YYYY-MM-DD'), 'MMDD')))
                     / COUNT (c.event_occurrence) < 91
            THEN
              'Quarterley'
            ELSE
              'Yearly'
         END
FROM     ps_ca_bp_events c
GROUP BY c.contract_num;
Boneist
  • 22,910
  • 1
  • 25
  • 40
Pradeep Choubey
  • 213
  • 1
  • 2
  • 4
  • 1
    What is the datatype of your event_dt column? Also, why are you converting your dates to strings and subtracting them? What is the purpose? If it's to find how many days are between the two dates, think about what result you might get if the max date was 12th December and the min date was 31st November in the same year. And what if they were in different years? – Boneist Apr 24 '15 at 16:14
  • event_dt column is of Date type. – Pradeep Choubey Apr 24 '15 at 16:18
  • 7
    if it's already a date, why are you doing `TO_DATE` on it?! – Boneist Apr 24 '15 at 16:19

3 Answers3

40

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.

Boneist
  • 22,910
  • 1
  • 25
  • 40
  • I Just removed the TO_DATE() function and the query is working fine... Thanks a lot for you prompt response... I really appreciate you man!! :) – Pradeep Choubey Apr 24 '15 at 16:39
3

This error can come not only because of the Date conversions

This error can come when we try to pass date whereas varchar is expected
or
when we try to pass varchar whereas date is expected.

Use to_char(sysdate,'YYYY-MM-DD') when varchar is expected

BuZZ-dEE
  • 6,075
  • 12
  • 66
  • 96
Ankur Bhutani
  • 3,079
  • 4
  • 29
  • 26
2

I added TO_DATE and it resolved issue.

Before modification - due to below condition i got this error

record_update_dt>='05-May-2017'

After modification - after adding to_date, issue got resolved.

record_update_dt>=to_date('05-May-2017','DD-Mon-YYYY')
Derrick
  • 3,669
  • 5
  • 35
  • 50
karthik G
  • 419
  • 5
  • 5