Re: ORA-01847 when to_char is used in where clause

From: Tim X <timx_at_nospam.dev.null>
Date: Thu, 21 Oct 2010 18:42:10 +1100
Message-ID: <87eibkuh4d.fsf_at_rapttech.com.au>

joel garry <joel-garry_at_home.com> writes:

> On Oct 20, 12:31 am, Pelle <petter.fryk..._at_gmail.com> wrote:

>> On 19 Oct, 17:33, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
>>
>>
>>
>> > "Pelle" <petter.fryk..._at_gmail.com> a écrit dans le message de news:
>> > 3df60d52-f531-4ed4-b3c5-3044f499a..._at_x42g2000yqx.googlegroups.com...
>> > | Hi!
>> > |
>> > | I have the following problem:
>> > |
>> > | select distinct to_char(wpa_timestamp, 'YYYY')
>> > | from view_waiting_time_book
>> > | where to_char(sysdate, 'YYYY') = '2010';
>> > |
>> > | gives me 13 rows back with distinct years, all ok so far. But the
>> > | following:
>> > |
>> > | select distinct to_char(wpa_timestamp, 'YYYY')
>> > | from view_waiting_time_book
>> > | where to_char(wpa_timestamp, 'YYYY') = '2010'
>> > |
>> > | gives me
>> > | ORA-01847: day of month must be between 1 and last day of month. The
>> > | to_char(...) in the where clause that does not work is exactly the
>> > | same as the to_char(...) in the select clause in both queries, also
>> > | the first one that works fine.
>> > |
>> > | The view_waiting_time_book is a quite complex view where two strings
>> > | are concatenated and converted into a date as wpa_timestamp, but it
>> > | still works fins to select with a to_char from it in the first
>> > | example.
>> > |
>> > | Please help! The version of Oracle is Oracle Database 10g Enterprise
>> > | Edition Release 10.2.0.5.0 - 64bit Production.
>> > |
>> > | Thanks!
>> > | ::Petter
>>
>> > If wpa_timestamp is of string datatype then TO_CHAR on it
>> > implies an implicit conversion that use your default date format
>> > which may not match your data.
>> > Explicit use a TO_DATE before TO_CHAR with the appropriate
>> > format.
>>
>> > Regards
>> > Michel
>>
>> Hi!
>>
>> Infact the wpa_timestamp is defined as a to_date from concatenated
>> strings and shows as a Date when I do desc on the view. I tried to put
>> another to_date(to_char around it both in the view definition and the
>> query on the view, and it still does not work.
>>
>> I am trying to reproduce with a complete sample that I can post here.
>>
>> Thanks!
>> ::Petter

>
> select dump(wpa_timestamp) from view_waiting_time_book;
> might tell you what strange thing is happening.  You may need a TRUNC
> in your to_char, or maybe showing us the wpa_timestamp definition
> might show some other possibility.
>
> jg
> --
> _at_home.com is bogus.
> http://web.archive.org/web/20020528233721/http://oracle.com/

given that in

> | select distinct to_char(wpa_timestamp, 'YYYY')
> | from view_waiting_time_book
> | where to_char(sysdate, 'YYYY') = '2010';

the where clause has no affect i.e. is true for all rows when executed this year, what happens if you omit it completely?

Also, maybe try

select distinct to_char(wpa_timestamp, 'YYYY') from view_waiting_time_book
where wpa_timestamp between '01-JAN-2010 00:00:00' AND '31-DEC-2010 24:59:59';

and

select distinct to_char(wpa_timestamp, 'YYYY') from view_waiting_time_book
where to_char(trunc(wpa_timestamp, 'YEAR'), 'YYYY') = '2010';

My guess is that you somehow have a bad date in that column. If it is of DATE type, I'm not sure how as it should have thrown an exeption when you tried to first insert it - at leat that is what I would have expected. Maybe even try a select and just print the value or wrap it in to_char (no distinct or where) if there are not too many rows.

Tim

-- 
tcross (at) rapttech dot com dot au

Received on Thu Oct 21 2010 - 02:42:10 CDT