OraFAQ Forum: Forms » date issue

Home » Developer & Programmer » Forms » date issue

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

date issue [message #505645]

Wed, 04 May 2011 06:55

lokeshsurana
Messages: 212
Registered: April 2010
Location: India

Senior Member

i am seting runtime where clause on Forms as follow:

	set_block_property('FORMC', onetime_where,'where to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') >='||'to_date('||:search.inv_frm||','||'DD-MON-YYYY'||')'||
	' and to_char(INVOICE_DATE,'||'DD-MON-YYYY'||') <' || 'to_date('||:search.inv_to||','||'DD-MON-YYYY'||')');

:search.inv_from is from date

:search.inv_to is up to date

i wann data between this dates

but date parameter is not working it showing all data......

where synatx get wrong..

Report message to a moderator

Re: date issue [message #505648 is a reply to message #505645]

Wed, 04 May 2011 06:58

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Probably because you're (explicitly) comparing dates to strings.

Compare dates to dates Smile

Report message to a moderator

Re: date issue [message #505649 is a reply to message #505645]

Wed, 04 May 2011 06:59

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

Senior Member

You're converting invoice_date to a string, and then matching it to some variables that you've converted to dates.
Compare dates to dates, not strings to dates.

Report message to a moderator

Re: date issue [message #505651 is a reply to message #505649]

Wed, 04 May 2011 07:01

lokeshsurana
Messages: 212
Registered: April 2010
Location: India

Senior Member

if i do to_daye at both end ...then no date is retiving

i checked by runing query on toad as:

select * from xx_ar_formc
 where to_date(INVOICE_DATE,'DD-MON-YYYY')>=to_date('12-DEC-2010','DD-MON-YYYY')
     and to_date(INVOICE_DATE,'DD-MON-YYYY') < to_date('24-DEC-2010','DD-MON-YYYY')

Report message to a moderator

Re: date issue [message #505652 is a reply to message #505651]

Wed, 04 May 2011 07:02

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Is INVOICE_DATE a date already? If it is, you don't want to be TO_DATE()'ing it.

CM: fixed typo on the word date.

[Updated on: Wed, 04 May 2011 07:05] by Moderator

Report message to a moderator

Re: date issue [message #505654 is a reply to message #505651]

Wed, 04 May 2011 07:04

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

Senior Member

So now you're converting a date to a date, which would be at best pointless.
Assuming invoice_date is a date you don't need to do anything to it.

Report message to a moderator

Re: date issue [message #505657 is a reply to message #505652]

Wed, 04 May 2011 07:12

lokeshsurana
Messages: 212
Registered: April 2010
Location: India

Senior Member

i am setting following where clause:

but still i amnot getting o/p

I think is it problem with " ' "

'DD-MON-YYYY'

In this we need to pass sinle quote as i think so...how to pass this single quote..?

	set_block_property('FORMC', onetime_where,'where INVOICE_DATE >='||'to_date('||:search.inv_frm||','||'DD-MON-YYYY'||')'||
	' and INVOICE_DATE < ' || 'to_date('||:search.inv_to||','||'DD-MON-YYYY'||')');

Report message to a moderator

Re: date issue [message #505659 is a reply to message #505657]

Wed, 04 May 2011 07:17

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

Senior Member

Same as with all dynamic sql - 2 quotes to get one. In this case just removing most of the || should solve it.

set_block_property('FORMC', onetime_where,'where INVOICE_DATE >= to_date('||:search.inv_frm||',''DD-MON-YYYY'')'
||' and INVOICE_DATE < to_date('||:search.inv_to||',''DD-MON-YYYY'')');

However, if the quotes were wrong - you should have got an error, either on setting the where clause or running the query.

[Updated on: Wed, 04 May 2011 07:18]

Report message to a moderator

Re: date issue [message #505660 is a reply to message #505659]

Wed, 04 May 2011 07:41

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

Senior Member
Account Moderator

But, in the first message it is said that form's items are DATEs. So it would be

set_block_property('formc', onetime_where, 'where invoice_date >= ' || :search.inv_from ||
                                           '  and invoice_date <  ' || :search.inv_to
                  );

Report message to a moderator

Re: date issue [message #505662 is a reply to message #505660]

Wed, 04 May 2011 07:52

lokeshsurana
Messages: 212
Registered: April 2010
Location: India

Senior Member

but invoice_date is stroing with timestamp in db..?

Report message to a moderator

Re: date issue [message #505664 is a reply to message #505662]

Wed, 04 May 2011 08:00

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

Senior Member

What, exactly, is the datetype of invoice_date?

Report message to a moderator

Re: date issue [message #505665 is a reply to message #505660]

Wed, 04 May 2011 08:01

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

Senior Member

Littlefoot wrote on Wed, 04 May 2011 13:41

But, in the first message it is said that form's items are DATEs. So it would be

set_block_property('formc', onetime_where, 'where invoice_date >= ' || :search.inv_from ||
                                           '  and invoice_date <  ' || :search.inv_to
                  );

Wouldn't it just be:

set_block_property('formc', onetime_where, 'where invoice_date >= :search.inv_from and invoice_date <  :search.inv_to');

Report message to a moderator

Previous Topic:	problem in run the form orale developer 10 g
Next Topic:	Tried to move Data Block Detail

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 07 15:16:13 CDT 2024