OraFAQ Forum: Forms » Date-comparison in default

Home » Developer & Programmer » Forms » Date-comparison in default_where clause (10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Date-comparison in default_where clause [message #508944]

Wed, 25 May 2011 06:25

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

Report message to a moderator

Re: Date-comparison in default_where clause [message #508950 is a reply to message #508944]

Wed, 25 May 2011 06:33

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

PROPOSAL_END column is, I presume (actually, I HOPE!) of a DATE datatype. If it is not, it should be.

As SYSDATE is a function that returns a DATE value, you'd simply compare PROPOSAL_END with SYSDATE - not transformation function is to be used (i.e. no TO_CHAR, TO_DATE, whatever). Simply

proposal_end <= sysdate

Report message to a moderator

Re: Date-comparison in default_where clause [message #508975 is a reply to message #508950]

Wed, 25 May 2011 07:40

Stefan21
Messages: 8
Registered: May 2011

Junior Member

With sysdate only, there are still the format-issues. But to cut the || and doing it by database was the right suggestion. Thank you for that. I solved it with:

proposal_end >= (select to_char(sysdate, ' 'DD.MM.YYYY' ') AS "MY_DATE_FORMAT" from dual);

Thanks for your help.
Stefan

Report message to a moderator

Re: Date-comparison in default_where clause [message #508977 is a reply to message #508975]

Wed, 25 May 2011 07:47

cookiemonster
Messages: 13937
Registered: September 2008
Location: Rainy Manchester

Senior Member

What datatype is proposal_end?

Report message to a moderator

Re: Date-comparison in default_where clause [message #508979 is a reply to message #508977]

Wed, 25 May 2011 07:50

Littlefoot
Messages: 21818
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

If PROPOSAL_END is DATE, you are comparing it to a string ("TO_CHAR" makes a string), which is wrong.

Report message to a moderator

Re: Date-comparison in default_where clause [message #508985 is a reply to message #508979]

Wed, 25 May 2011 08:25

Stefan21
Messages: 8
Registered: May 2011

Junior Member

The datatype is date. But to_char is working eitherway. I changed it to to_date, the same result is displayed.
I know, to_date is the correct one, but can you explain, why to_char is accepted too?

edit:

sysdate only is now working properly too. Dunno why it didn't before.

[Updated on: Wed, 25 May 2011 08:51]

Report message to a moderator

Re: Date-comparison in default_where clause [message #508995 is a reply to message #508985]

Wed, 25 May 2011 09:07

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.

Report message to a moderator

Previous Topic:	Navigation between Forms via custom-menu
Next Topic:	Session variable values overwritten between transactions in different sessions

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 15:37:48 CDT 2024