Help with this Query Pls

[aj_usa]

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

[stecal]

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.

[aj_usa]

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

[stecal]

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

[aj_usa]

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

[aj_usa]

Problem solved, thanks for your inputs