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;

try select to_date('20140301','YYYYMMDD') from dual and check the output — Vikas Hardia, Mar 16 '14 at 17:15
i wonder why it should work in select clause but not in where clause — Joggl, Mar 16 '14 at 17:20
@GordonLinoff no but its all the content when using "and creation_time_ > to_timestamp('20140307','YYYYMMDD')" — Joggl, Mar 16 '14 at 17:29
This because filter `value1 <> '0'` for some reasons may be applied AFTER your conversion `to_date(value1,'DD.MM.YYYY')`. Probably, this is an Oracle "feature", that looks like a bug. — Egor Skriptunoff, Mar 16 '14 at 17:31

score 5 · Accepted Answer · answered Mar 16 '14 at 17:31

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.

+1 @Joggl Suspected this, but your comment to [previous question](http://stackoverflow.com/q/22426607/2899629) threw me off-guard. — Yaroslav Shabalin, Mar 17 '14 at 08:45

score 0 · Answer 2 · answered May 06 '20 at 02:44

0

If you're using OracleParameter in SQL with parameter name and value binding, check you set the oracleCommand.BindByName = true then it'll bind by name, and not by parameter adding order.

answered May 06 '20 at 02:44

yu yang Jian

6,680
7
55
80

score 0 · Answer 3 · answered Jan 19 '23 at 00:23

I just want to add that we got the same ORA-01847 error when the user's web browser language encoding was changed from English to Portuguese.

The line of code that failed was:

IF TO_DATE(NEW_DATE,'DD-MON-YYYY') = TO_DATE(OLD_DATE,'DD-MON-YYYY') THEN

Which was fixed by rewriting it to:

IF TO_CHAR(NEW_DATE,'DD-MON-YYYY') = TO_CHAR(OLD_DATE,'DD-MON-YYYY') THEN

It appears Oracle detected the change of language and assumed the date value will be formatted for the Portuguese locale.

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

3 Answers3

Linked