0

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?

to_date(val_strg1,'DD-MON-YYYY')

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?

Haytem BrB
  • 1,528
  • 3
  • 16
  • 23
SMORF
  • 499
  • 6
  • 13
  • 30

4 Answers4

1

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.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • Unfortunately, I cannot change the column type. It is indeed a date inserted in a text column but, I cannot access the column (this is a very historic set up in our system and cannot be changed). Is there another way of using the text as a date? – SMORF Feb 08 '16 at 13:49
  • If you cannot change the table design then maybe you can at least add a check constraint that ensures the date format. – Thorsten Kettner Feb 08 '16 at 14:02
0

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).
Bye,
Igor

user1
  • 556
  • 2
  • 6
  • 22
0

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.

SMORF
  • 499
  • 6
  • 13
  • 30
0

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;
Avadhani Y
  • 7,566
  • 19
  • 63
  • 90
Jan Kalina
  • 1