ORA-01847 day of month must be between 1 and last day of month - but data is OK

Question

Problem is solved - see end of this post.

when i call to_date in select clause everything works fine - get a resultset of 12 records:

select value1,to_date(value1,'DD.MM.YYYY') 
  from variableindex 
  where 
    value1 is not null 
    and value1 <> '0' 
    and creation_time_ > to_timestamp('20140307','YYYYMMDD')
  order by 2

returns

'VALUE1'     'TO_DATE(VALUE1,'DD.MM.YYYY')'
'25.11.2013' 25.11.13
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'12.03.2014' 12.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'14.03.2014' 14.03.14
'20.03.2014' 20.03.14
'20.03.2014' 20.03.14

Every datestring has been converted as expected.

If i add the following line to where clause

and to_date(value1,'DD.MM.YYYY') < to_date('20140301','YYYYMMDD')

i'll receive:

ORA-01847: Tag des Monats muss zwischen 1 und letztem Tag des Monats liegen
01847. 00000 -  "day of month must be between 1 and last day of month"
*Cause:    
*Action:

No it really gets nasty... i changed my query to

where id_ in (...)

and used the same 12 recordsets ids as in original query. No Error...

Many thanks to @GordonLinoff - this is how i use the query now:

select value1,to_date(value1,'DD.MM.YYYY') from variableindex 
   where 
   (case when value1 <> '0'  then to_date(value1,'DD.MM.YYYY') end) >  to_timestamp('20131114','YYYYMMDD')
   and creation_time_ > to_timestamp('20140307','YYYYMMDD')
order by 2;

Answer 1

This is your query with the where clause:

select value1, to_date(value1,'DD.MM.YYYY') 
from variableindex 
where value1 is not null and
      value1 <> '0' and
      creation_time_ > to_timestamp('20140307', 'YYYYMMDD') and
      to_date(value1 'DD.MM.YYYY') < to_date('20140301', 'YYYYMMDD')
order by 2;

Oracle does not guarantee the order of processing of clauses in the where. So, value <> '0' is not guaranteed to run before the last condition. This happens to be a big problem on SQL Server. One solution is to use a case statement:

select value1,to_date(value1, 'DD.MM.YYYY') 
from variableindex 
where value1 is not null and
      value1 <> '0' and
      creation_time_ > to_timestamp('20140307', 'YYYYMMDD') and
      (case when value <> '0' then to_date(value1, 'DD.MM.YYYY') end) <
          to_date('20140301', 'YYYYMMDD')
order by 2;

Rather ugly, but it just might solve your problem.