Home » Developer & Programmer » Forms » dates problem (Oracle 6i form Builder)
dates problem [message #410105] Thu, 25 June 2009 04:45 Go to next message
asmani
Messages: 47
Registered: February 2007
Member
Hi there
I am having problem to fetch data in grid. My where clase is

v_where VARCHAR2(1000) := ' staff_party_no = ' ||''''||:xxcar_control_blk.party_number ||''''||
' and to_char(start_time,''DD/MM/YY'') >= '||to_char(:xxcar_control_blk.from_Date,'''DD/MM/YYYY''')||
' and to_char(start_time,''DD/MM/YY'') <= '||to_char(:xxcar_control_blk.to_Date,'''DD/MM/YYYY''')||
' and (('''||:xxcar_control_blk.show_inactive || ''' = ''Y'' AND status = ''I'') OR status = ''A'')'
;
It works on one month like from 01-JUN-2009 to 30-JUN-2009 but I change the date like from 01-JUN-2009 to 01-JUL-2009, it does work .

Any idea............
Thanks
Usman Ahmad.
Re: dates problem [message #410111 is a reply to message #410105] Thu, 25 June 2009 05:57 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
You are filter on char not on date.
By the way what are start_time and from_Date?

By
Vamsi
Re: dates problem [message #410112 is a reply to message #410111] Thu, 25 June 2009 06:03 Go to previous messageGo to next message
asmani
Messages: 47
Registered: February 2007
Member
Thank for your reply.
Start_time is a table column name, :xxcar_control_blk.from_Date and :xxcar_control_blk.to_Date are form felids.

query would be like that

select * from x
where x.start_time between :xxcar_control_blk.from_Date and :xxcar_control_blk.to_Date;

start_time is a full time stemp thats why my doing trunc and to_char and on form date is in 01-JUN-2009 format(in both felids).

Usman

[Updated on: Thu, 25 June 2009 06:06]

Report message to a moderator

Re: dates problem [message #410113 is a reply to message #410105] Thu, 25 June 2009 06:16 Go to previous messageGo to next message
cookiemonster
Messages: 12409
Registered: September 2008
Location: Rainy Manchester
Senior Member
to_char(start_time,''DD/MM/YY'')
to_char(:xxcar_control_blk.from_Date,'''DD/MM/YYYY''')


You're using different format masks.
I'd convert the datablock items to timestamp and use BETWEEN.
Don't to_char them.

Ask yourself which of these dates is the greatest according to date ordering and according to char ordering:
01-JUN-2009
10-MAY-2009
Re: dates problem [message #410114 is a reply to message #410105] Thu, 25 June 2009 06:21 Go to previous message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
usman_nato wrote on Thu, 25 June 2009 11:45
It works on one month like from 01-JUN-2009 to 30-JUN-2009 but I change the date like from 01-JUN-2009 to 01-JUL-2009, it does work .

Any idea............

Maybe you shall realize the difference between string and date.
to_char(start_time,''DD/MM/YY'') is string, so string comparition rules are applied on it. Based on them, '01-JUL-2009' < '30-JUN-2009' as the first letter is smaller ('0' < '3')
Suggestion: compare dates instead of strings. As you did not post the types of all involved columns and bind variables, I cannot add anything more without guessing.
Well, maybe one hint not regarding the sorting: read about binding and use it instead of hardcoding values. Before any actions, write down the content of V_WHERE and check it.
Previous Topic: Year Date's and days come in form
Next Topic: Highlihting a record in table which was displayed in a form (merged 2 ) 10g
Goto Forum:
  


Current Time: Tue Dec 06 08:49:26 CST 2016

Total time taken to generate the page: 0.05552 seconds