1

I created an easier to test post here: ORA-01847 day of month must be between 1 and last day of month - but data is OK Problem solution described there

It took me hours to search for a comparable issue in the web - finally i think only you out there can help me.

I send my Query to Oracle 11 via SQL developer.

You may ignore the complete subselect (its in pastebin) - i added a dump of each column to see details.

I got big Problems when comparing timestamps in where clause: here my basic sql without where clause in last line:

select prod,operating,fakeday,prod_origin,operating_origin,
dump(prod),dump(fakeday),dump(operating) from 
(
http://pastebin.com/wqX7dJPA
) sub
-- works: where sub.operating < to_timestamp('20140101','YYYYMMDD')
-- works: where sub.fakeday < to_timestamp('20140101','YYYYMMDD')
-- works NOT: where sub.prod < to_timestamp('20140101','YYYYMMDD')

Result without where clause is:

    'PROD'|                    'OPERATING'|                'FAKEDAY'|                  'PROD_ORIGIN'|'OPERATING_ORIGIN'|'DUMP(PROD)'|                                          'DUMP(FAKEDAY)'|                                              'DUMP(OPERATING)'
    25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|25.11.13 00:00:00,000000000|',20131125,'|'25.11.2013'|      'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,11,25,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
    24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|24.02.14 00:00:00,000000000|',20140224,'|'24.02.2014'|      'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,2,24,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
    28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'|      'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
    28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|28.10.13 00:00:00,000000000|',20131028,'|'28.10.2013'|      'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,221,10,28,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'
    20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|20.01.14 00:00:00,000000000|',20140120,'|'20.01.2014'|      'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'|'Typ=187 Len=20: 7,222,1,20,0,0,0,0,0,0,0,0,0,0,3,0,0,0,0,0'

If i add the where clause in last line filtering on 'prod' i'll run into 

ORA-01830: Datumsformatstruktur endet vor Umwandlung der gesamten Eingabezeichenfolge
01830. 00000 -  "date format picture ends before converting entire input string"
*Cause:    
*Action:

If i'm using the other column sub.operating or sub.fakeday i'll receive correct filtered resultlist.

where sub.operating < to_timestamp('20140101','YYYYMMDD')

another important information - i can call

select prod,operating,prod-operating,fakeday

and it will work -> shows a interval result for prod-operating

BUT 

where prod - operating = INTERVAL '0' DAY;

will lead to: 

 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"

Please help - i don't have any idea.

Community
  • 1
  • 1
Joggl
  • 83
  • 1
  • 2
  • 6
  • i moved subselect to pastebin – Joggl Mar 15 '14 at 17:59
  • Have you checked if **all** values in `tor.prod_days` and `lvx.value1` can be be converted to timestamp with specified mask? Are there only 5 rows you showed us or some more? – Yaroslav Shabalin Mar 15 '14 at 20:59
  • i can even reduce it to one row resultset. I do not understand that the conversion to timestamp works - see column prod - but the comparision to another timestamp does not work – Joggl Mar 15 '14 at 21:31
  • another important information - i can call select prod,operating,prod-operating,fakeday and it will work -> shows a interval result – Joggl Mar 15 '14 at 22:08
  • i figured our a new very simple query where i was able to repro a similar problem - 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: i'll create a new post for it – Joggl Mar 16 '14 at 16:15

1 Answers1

0

alter session set nls_date_format = 'DD.MM.YYYY HH24:MI:SS';

PepitoPerez
  • 1