Muthu,

Try creating a view which first removes the timeby using the cast and then
selecting on the view.

Bill Meaney

===============================================================


Bill,

Thanks for your time.

Yes you are right, RECEIPT.RECEIPTDATE has time also.

I already tried that.

The problem is in group by I am not able to use cast , it is
throwing "token unknown error"

Regards,

Muthu Annamalai

--- In firebird-support@yahoogroups.com, "Bill Meaney"
<wameaney@t...> wrote:

> Muthu,
>
> Try changing the group by to:
>
> GROUP BY CAST(RECEIPT.RECEIPTDATE AS DATE)
>
> I assume the RECEIPT.RECEIPTDATE has the time also.
>
> Bill Meaney
>
> =============================================================
> Dear Friends,
>
> I need help with my SP. I have given my SP below.
> ----------------------SP------------------------
> CREATE PROCEDURE REPORTWEEKSALES
> ( FROMDATE DATE )
> RETURNS
> (
> SALESDAY DATE,
> RECEIPTS INTEGER,
> SALES DECIMAL(18,2),
> AVERAGESALES DECIMAL(18,2)
> )
> AS
>
> BEGIN
>
> FOR SELECT CAST(RECEIPTDATE AS DATE),
> COUNT(CAST(RECEIPT.RECEIPTNUMBER AS INTEGER)),
> SUM(RECEIPT.AMOUNT),
> AVG(RECEIPT.AMOUNT)
> FROM RECEIPT
> WHERE CAST(RECEIPT.RECEIPTDATE AS DATE)
> BETWEEN :FROMDATE AND :FROMDATE + 6
> GROUP BY RECEIPTDATE
> INTO :SALESDAY,:RECEIPTS,:SALES,:AVERAGESALES
>
> DO SUSPEND;
> END
> ---------------------SP end-------------------------
>
> This SP is returning
>
> salesday receipts sales averagesales
> 18.6.2004 1 50 50
> 18.6.2004 1 45 45
>
> What I want is
>
> salesday receipts sales averagesales
> 18.6.2004 2 95 47.5
>
> Need Help
>
> Thanks
>
> Muthu Annamalai
>
>
>
>
>
>
>
> Yahoo! Groups Links

Yahoo! Groups Links