date issue [message #505645] |
Wed, 04 May 2011 06:55 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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..
|
|
|
|
Re: date issue [message #505649 is a reply to message #505645] |
Wed, 04 May 2011 06:59 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: date issue [message #505651 is a reply to message #505649] |
Wed, 04 May 2011 07:01 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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')
|
|
|
|
Re: date issue [message #505654 is a reply to message #505651] |
Wed, 04 May 2011 07:04 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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.
|
|
|
Re: date issue [message #505657 is a reply to message #505652] |
Wed, 04 May 2011 07:12 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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'||')');
|
|
|
Re: date issue [message #505659 is a reply to message #505657] |
Wed, 04 May 2011 07:17 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #505665 is a reply to message #505660] |
Wed, 04 May 2011 08:01 ![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 |
|
|
Littlefoot wrote on Wed, 04 May 2011 13:41But, 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');
|
|
|