Need help with SQL expression statement

[ewarr]

The below SQL works great and pulls invoices for 2007 and then sums dollars by product name.
(
select sum("Sales_Dollars")
from "AgdataView" A
where A."Agdata Product Name" = "AgdataView"."Agdata Product Name" and
{fn year(A."Invoice_Date")} = 2007
)

Now I need to use a date range and my SQL isn't working:

(
select sum("Sales_Dollars")
from "AgdataView" A
where A."Agdata Product Name" = "AgdataView"."Agdata Product Name" and
"AgdataView"."Invoice_Date" >= 10/1/2007 and "AgdataView"."Invoice_Date" <= 9/30/2008
)

Can you help?

 

[IanWaterman]

What DB are you using your syntax for dates is wrong. In sQL server you will need to CAST your text string to a date for Oracle you need to use To_date

To_date('10/01/2007', 'DD/MM/YYYY')

Ian