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
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
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
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') ;