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

"Pelle" <petter.frykman_at_gmail.com> a écrit dans le message de news: 3df60d52-f531-4ed4-b3c5-3044f499a5ca_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 Received on Tue Oct 19 2010 - 11:33:26 CDT