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

From: joel garry <joel-garry_at_home.com>
Date: Wed, 20 Oct 2010 09:46:49 -0700 (PDT)
Message-ID: <d884ea74-4670-4c3e-92c0-295f49457349_at_o11g2000prf.googlegroups.com>



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/
Received on Wed Oct 20 2010 - 11:46:49 CDT

Original text of this message