Hi,
I have the following sample table and data. The date column SHOULD HAVE BEEN DECLARED AS DATE but it is text(legacy system).
There are some records which contain invalid data in this column. So, in order to exclude these rows,
i wrote the following:
with sample_data(dt) as
(select '00/02/2015' from dual union all
select '10/02/2015' from dual union all
select '02/2015' from dual union all
select '13/02/2015' from dual union all
select '29/02/2015' from dual union all
select '29/02/2016' from dual union all
select '13/06/2015' from dual union all
select '13/06/2005' from dual union all
select '01//2015' from dual union all
select null from dual)
select *
from
(
select dt
from sample_data
where dt in (select to_char(to_date('01/01/2013')+rownum-1,'dd/mm/yyyy')
from dual
connect by level<=2000)
)
where to_date(dt,'dd/mm/yyyy') between to_date('01/01/2000', 'dd/mm/yyyy') and to_date('01/01/2020', 'dd/mm/yyyy')
However the problem persists....
The problem displayed is ORA-01847: day of month must be between 1 and last day of month.
How should i write the query ?
Note: I use oradb v.11.2
Thanks,
Sim