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

"Pelle" <petter.frykman (AT) gmail (DOT) com> a écrit dans le message de news:
3df60d52-f531-4ed4-b3c5-3044f499a5ca...oglegroups.com...
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

On 19 Oct, 17:33, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
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

On Oct 20, 12:31*am, Pelle <petter.fryk... (AT) gmail (DOT) com> wrote:
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
--
@home.com is bogus.
http://web.archive.org/web/200205282...://oracle.com/

joel garry <joel-garry (AT) home (DOT) com> writes:

given that in

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

"Pelle" <petter.frykman (AT) gmail (DOT) com> wrote

On 19 Oct, 17:33, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
Hi, Peter.

Is your name really ""Pelle" <petter.frykman (AT) gmail (DOT) 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?

On 21/10/10 11:17, Jack wrote:
It's Petter, not Peter. And Frykman, not Fryckman.

You, my friend, are an illiterate idiot.

Also: You really think any local celebrity owns his name (=email-addresses)
in every conceivable domain, around the globe?

No, it's not. It's a finnish word meaning clown. Also common first name in
Nordic countries.

And yes, the Fryckman guy is a clown. A thief also, four years in jail.

Not clear to anyone else because the article you point is in Finnish. (Not
that it would be interesting in anywhere else.)

Not worth even that.

Obviusly this "Jack" is a finnish guy and Fryckman fan boy, otherwise he
wouldn't have bothered. As a fellow Finn I'm ashamed of the stupidity of
some people here, thus this follow-up.

Sorry for offtopic.
--
Tuomas - VWs:'63 typ14, '65 typ34 & '61 typ2