Hi,
I have a table with 10 million rows. There is a column updatedate. This data comes from external source and I need to load the data into a table by doing some validations. When I do TO_DATE(date_col, 'yyyy-mm-dd hh24:mi:ss') it errors out as some of the data is in format 'MON DD YYYY'. Doing to_date conversion of this date format is throwing error.
Oracke version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
PL/SQL Release 12.1.0.2.0 - Production
create table date_test(date_col varchar2(15));
insert into date_test values('2020-01-01');
insert into date_test values('2020-02-01');
insert into date_test values('2020-03-01');
insert into date_test values('2020-04-01');
insert into date_test values('Aug 07 2020');
select to_date(date_col, 'yyyy-mm-dd hh24:mi:ss') from date_test;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified ranges') from date_test;
ORA-01841: (full) year must be between -4713 and +9999, and not be 0
01841. 00000 - "(full) year must be between -4713 and +9999, and not be 0"
*Cause: Illegal year entered
*Action: Input year in the specified range
I would like to find all rows where the date format is valid but entered differently.
please help. I appreciate your help
Thanks
kumar