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

"Pelle" <petter.frykman_at_gmail.com> wrote in message news:715afc55-051e-43c2-964a-58485843e82a_at_j25g2000yqa.googlegroups.com... 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

Hi, Peter.

Is your name really ""Pelle" <petter.frykman_at_gmail.com> "

This one: http://fi.wikipedia.org/wiki/Peter_Fryckman

If your name is not that, change your email address ASAP! Note: Pelle = Clowns in english
Just my 0,05? Received on Thu Oct 21 2010 - 03:17:54 CDT