Date-comparison in default_where clause [message #508944] |
Wed, 25 May 2011 06:25 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.gravatar.com/avatar/d069370bb99042bc9ae7bf5f892d05ec?s=64&d=mm&r=g) |
Stefan21
Messages: 8 Registered: May 2011
|
Junior Member |
|
|
Hi experts,
Could it be that it's impossible to change the date format in the default_where clause?
The table column PROPOSAL_END in the database that I want to compare with, is in Format DD.MM.YYYY.
I tried:
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date(sydate,'DD.MM.YYYY')));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --]));
set_block_property('Tours' , default_where, 'Number_of_places > 0 AND PROPOSAL_END <= ' || to_char(to_date([-- A date item with the intial value $$date$$ the output is in Fomat DD.MM.YYYY by default --],'DD.MM.YYYY')));
It all does dot matter. Everytime the generated select-statement shows the format DD-MMM-YY. How can I change that?
Thanks for your help.
Stefan
|
|
|
|
|
|
|
|
Re: Date-comparison in default_where clause [message #508995 is a reply to message #508985] |
Wed, 25 May 2011 09:07 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) |
cookiemonster
Messages: 13937 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
Because to_date is also wrong. A date is a date, it needs no conversion.
to_date does not accept a date parameter for that reason, it accepts char. So if you pass a date to it oracle implicitly converts the date to a char.
So:
to_date(sysdate,'DD.MM.YYYY')
Is really:
to_date(to_char(sysdate, '<default format mask>'),'DD.MM.YYYY')
How dates are displayed has nothing to do with how they are stored. They are actually stored as a set of bytes that isn't really human readable.
Whenever you query a date oracle uses the default format mask to display them.
|
|
|