Home Ask Login Register

Developers Planet

Your answer is one click away!

SMORF February 2016

oracle sql - a non-numeric character was found where a numeric was expected

I know there are loads of postings regarding fixing this error but, I'm just not understanding it!

val_strg1 value is 01.04.2016. I want to use this and not show lines where this date is older than current date, (i.e. and trunc(sysdate) < dv.val_strg1).

But, even though I have used a to_date format, I still get the a non-numeric character was found where a numeric was expected error?

I have tried several to_date formats;

to_date(val_strg1,'DDMMYYYY'), to_date(val_strg1,'DD-MM-YYYY')

The following gives me a 'not a valid month' error?


My script...

select val_strg, val_strg1, to_date(val_strg1,'DDMMYYYY')
from sd_domainval_org 
where name = 'HYPERCARE_CUNR'
order by sort_no

How can I use the val_strg1 as a date?


Dunno February 2016

if your string date val_strg1 is in the form 'DD.MM.YYYY' (i.e. '01.04.2016'), then you have to use to_date(val_strg1,'DD.MM.YYYY');
for example: Select to_date('01.04.2016','DD.MM.YYYY') from dual;
If you have errors again, probably you have a string in the recordset that is not in a valid form for the to_date function (check the values in the val_strg1 column).

Thorsten Kettner February 2016

It seems you are using a text column (e.g. VARCHAR2) for the date. And you are saying that

to_date(val_strg1, 'DD.MM.YYYY')

results in an error. So you have a value in that column that does not match the pattern. Here is a query to find such invalid entries:

select * 
from domainval
where name = 'HYPERCARE_CUNR'
and not regexp_like(val_strg1, '^[[:digit:]]{2}\.[[:digit:]]{2}\.[[:digit:]]{4}$');

You can then correct the wrong entries, but a better solution would of course be not to store dates in string columns at all. Use date columns instead, so as to not have such issues.

SMORF February 2016

I found an answer...

and trunc(sysdate) < to_date(regexp_substr(val_strg1, '^[[:digit:]]{2}\.[[:digit:]]{2}\.[[:digit:]]{4}$'),'DD.MM.YYYY')

...seems to work OK.

Jan Kalina February 2016

you have to tune formating string to exactly match your date structure in this case

select val_strg, val_strg1, to_date(val_strg1,'DD.MM.YYYY')
from sd_domainval_org 
where name = 'HYPERCARE_CUNR'
order by sort_no;

Post Status

Asked in February 2016
Viewed 2,843 times
Voted 6
Answered 4 times


Leave an answer

Quote of the day: live life