| dates problem [message #410105] |
Thu, 25 June 2009 04:45  |
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 #410112 is a reply to message #410111] |
Thu, 25 June 2009 06:03  |
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 |
cookiemonster
Messages: 13938
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 |
flyboy
Messages: 1903
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.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
