CODEWITHSUNDEEP
sqloracle-database
joe
joe

Reputation: 79

ORA-01722: invalid number in plsql

if i used like this im not get any error

(ph.x_date >= to_date('01/05/2016') and ph.x_date <= to_date('01/04/2020'))

but if range is big like this

(ph.x_date >= to_date('01/05/2002') and ph.x_date <= to_date('01/04/2020'))

i get invalid number error. where is my mistake. Thanks

Upvotes: 2

Views: 264

Answers (3)

Katerina D.
Katerina D.

Reputation: 11

you need to specify the format of date, e.g. to_date('01/05/2002','DD/MM/YYYY') and make sure the date format you get from the table is the same as you specified

Upvotes: 1

psaraj12
psaraj12

Reputation: 5072

I think your data in SELECT list is having an issue

Please check the output of the below command and see whether it returns the output

 select *
 from  data ph 
 where ph.x_date >= to_date('01/05/2002') and 
 ph.x_date <=to_date('01/05/2016')  ;

Upvotes: 0

Himanshu
Himanshu

Reputation: 3970

To_date() has to be always used when you know the date data would come in a particular format always that you know before hand. Use to_date like this To_date('date', <dateformat>) this reads the date in the explicit format that you may want to make it read.

In your case it reads default but what if your order of retrieval changes in your x_date i guess your data in xdate column has some issues. Check data before 2016. Different order like mm/yy/dd or etc data different from the to_date format is there

Upvotes: 1

Related Questions