-
Help with this Query Pls
Hello All,
I need little help with this query. I want the output similar to the First output shown here and which is correct .
select to_char(a.start_date,'mm/dd/yyyy hhAM') Stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
a.SHOWTYPE, a.DESCTV
from acntv.SHOWS a,intra.pbb_batch_sum b
where a.Start_date between
to_date('07242003070000','MMDDYYYYHH24MISS')
and to_date('07252003035959','MMDDYYYYHH24MISS')
and a.batch=b.batch(+)
group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
a.SHOWTYPE, a.DESCTV ;
stdt endt SHOWTYPE DESCTV
--------------- --------------- -------------------------------- --------------------
07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie
07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi
07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
07/25/2003 03AM 07/25/2003 07AM JEWELRY Jewelry with Robert
If I happen to pass parameter as start date and end date, I get different result,since our date of operation is different and I need the same output as the first result shown above. Can somebody help me?
select to_char(a.start_date,'mm/dd/yyyy hhAM')stdt,to_char(a.end_date,'mm/dd/yyyy hhAM')endt,
a.SHOWTYPE, a.DESCTV
from acntv.SHOWS a,intra.pbb_batch_sum b
where a.Start_date between
to_date('&st','MMDDYYYYHH24MISS')
and to_date('&en','MMDDYYYYHH24MISS')
and a.batch=b.batch(+)
group by to_char(a.start_date,'mm/dd/yyyy hhAM'),to_char(a.end_date,'mm/dd/yyyy hhAM'),
a.SHOWTYPE, a.DESCTV ;
Enter value for st: 07242003
old 7: to_date('&st','MMDDYYYYHH24MISS')
new 7: to_date('07242003','MMDDYYYYHH24MISS')
Enter value for en: 07252003
old 8: and to_date('&en','MMDDYYYYHH24MISS')
new 8: and to_date('07252003','MMDDYYYYHH24MISS')
stdt endt SHOWTYPE DESCTV
--------------- --------------- -------------------------------- --------------------------------
07/24/2003 10PM 07/25/2003 03AM GEMSTONES Gemstones with Tommy
07/24/2003 12PM 07/24/2003 05PM JEWELRY Jewelry with Heidi
07/24/2003 05PM 07/24/2003 10PM JEWELRY Jewelry with Misty
07/24/2003 03AM 07/24/2003 07AM GEMSTONES Gemstones with Geoff and Cheryl
07/24/2003 07AM 07/24/2003 12PM JEWELRY Jewelry with Angie
Pls suggest how I should do it.
Thanks
-
Have you tried passing in the complete date/time group? The second case - looks like all you are passing in is the start date with no (explicit) time component.
-
No, I am not passing the complete date/time group. I just need the user to input the date. Is this possible ?
If not, can we he just pass the date and we concatenate the time string ? if so, how to do this ?
Thanks
-
A reason why your output could/would be different is because of the difference between
where a.Start_date between
to_date('07242003070000','MMDDYYYYHH24MISS')
and to_date('07252003035959','MMDDYYYYHH24MISS')
and
Enter value for st: 07242003[no time here]
old 7: to_date('&st','MMDDYYYYHH24MISS')
new 7: to_date('07242003','MMDDYYYYHH24MISS')
Enter value for en: 07252003[no time here]
old 8: and to_date('&en','MMDDYYYYHH24MISS')
new 8: and to_date('07252003','MMDDYYYYHH24MISS')
So your a.start_date between range is going to be different
-
Yes, if I pass 07242003070000 and 07252003035959 in the sql,
where a.Start_date between to_date('07242003070000','MMDDYYYYHH24MISS')
and to_date('07252003035959','MMDDYYYYHH24MISS') , I will get the desired output.
I want to know if there is any way,the user just enters 07252003 and 07252003 & if I can hard-code 070000 and 035959 or concatenate the time(hard-code) with the user entered date? I need this time constant,he can enter the dates.
Pls advise.
Thanks
-
Problem solved, thanks for your inputs
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|