Search notes:

ORA-01841 with select statements using redundant BETWEEN in select statements on table partitioned by range / month or year

create table tq84_part_by_range_year  (dt date, col_1 number) partition by range (dt) interval ( numtoyminterval(1, 'year' )) (partition p_0 values less than (date '0001-01-01'));
create table tq84_part_by_range_month (dt date, col_1 number) partition by range (dt) interval ( numtoyminterval(1, 'month')) (partition p_0 values less than (date '0001-01-01'));
create table tq84_part_by_range_day   (dt date, col_1 number) partition by range (dt) interval ( numtodsinterval(1, 'day'  )) (partition p_0 values less than (date '0001-01-01'));

select col_1 from tq84_part_by_range_year  where dt between date '2008-10-01' and date '2008-10-30' or dt between date '2008-10-30' and date '2008-10-01';
/* ORA-01841: (full) year must be between -4713 and +9999, and not be 0 */

select col_1 from tq84_part_by_range_month where dt between date '2008-10-01' and date '2008-10-30' or dt between date '2008-10-30' and date '2008-10-01';
/* ORA-01841: (full) year must be between -4713 and +9999, and not be 0 */

select col_1 from tq84_part_by_range_day   where dt between date '2008-10-01' and date '2008-10-30' or dt between date '2008-10-30' and date '2008-10-01';
/* OK */

select col_1 from tq84_part_by_range_day   where
  dt between to_date ('2008-10-01 05:17:13', 'yyyy-mm-dd hh24:mi:ss') and to_date ('2008-10-01 15:17:13', 'yyyy-mm-dd hh24:mi:ss') or
  dt between to_date ('2008-10-01 15:17:13', 'yyyy-mm-dd hh24:mi:ss') and to_date ('2008-10-01 05:17:13', 'yyyy-mm-dd hh24:mi:ss');
/* OK */  
    
select col_1 from tq84_part_by_range_day   where
  dt between to_date ('2008-10-01 05:17:13', 'yyyy-mm-dd hh24:mi:ss') and to_date ('2008-10-30 15:17:13', 'yyyy-mm-dd hh24:mi:ss') or
  dt between to_date ('2008-10-30 15:17:13', 'yyyy-mm-dd hh24:mi:ss') and to_date ('2008-10-01 05:17:13', 'yyyy-mm-dd hh24:mi:ss');
/* OK */

drop table tq84_part_by_range_year  purge;
drop table tq84_part_by_range_month purge;
drop table tq84_part_by_range_day   purge;

Github repository Oracle-Patterns, path: /DatabaseObjects/Tables/Partitions/ora-01841.sql

Apparently, this error can be suppressed by setting the undocumented parameter _extended_pruning_enabled.

Fatal error: Uncaught PDOException: SQLSTATE[HY000]: General error: 8 attempt to write a readonly database in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php:78 Stack trace: #0 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(78): PDOStatement->execute(Array) #1 /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php(30): insert_webrequest_('/notes/developm...', 1759414333, '216.73.216.42', 'Mozilla/5.0 App...', NULL) #2 /home/httpd/vhosts/renenyffenegger.ch/httpsdocs/notes/development/databases/Oracle/objects/tables/partitions/ora-01841(70): insert_webrequest() #3 {main} thrown in /home/httpd/vhosts/renenyffenegger.ch/php/web-request-database.php on line 78

ORA-01841 with select statements using redundant BETWEEN in select statements on table partitioned by range / month or year

See also