Re: ORA-01847 when to_char is used in where clause
From: Pelle <petter.frykman_at_gmail.com>
Date: Wed, 20 Oct 2010 00:31:37 -0700 (PDT)
Message-ID: <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
Date: Wed, 20 Oct 2010 00:31:37 -0700 (PDT)
Message-ID: <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
Received on Wed Oct 20 2010 - 02:31:37 CDT