Sql Query Error when passsing date parameters

29 views Asked by At
    select  Products.prodname, sum( billdetail.Qty) as qty,BillDetail.rate, 
    sum(billdetail.amt) as Amt from billdetail
    inner join products 
    on products.prodid = billdetail.prodid
    inner join category
    on category.catid = products.catid
    inner join billmaster 
    on billmaster.BillID = BillDetail.BillID
    where billmaster.billdate between 1/08/2017 and 19/09/2017 and CatName = 
    'chicken'
    group by prodname,rate

My issue is when I include date parameters it stops working. How can I include them?

3

There are 3 answers

0
AudioBubble On

Dates and string literals should be enclosed between two '':

select  
  Products.prodname, 
  sum( billdetail.Qty) as qty,
  BillDetail.rate, 
  sum(billdetail.amt) as Amt 
from billdetail
inner join products on products.prodid = billdetail.prodid
inner join category on category.catid = products.catid
inner join billmaster on billmaster.BillID = BillDetail.BillID
where billmaster.billdate between '20170801' and '20170919' 
  and CatName = 'chicken'
group by prodname,rate;

Also it is recommended to use the iso standard format to format the values of these dates' values.

0
Ryan Gadsdon On

Date needs '' = '1/08/2017' and '19/09/2017'

You also need from clause before join

0
marc_s On

First of all, the date as string literal has to be in single quotes - and secondly, I'd recommend to always use the ISO-8601 format of YYYYMMDD which works with any language/regional settings (other formats are depending on the current language/regional settings and might work on some systems, but fail on others).

So try:

WHERE
    billmaster.billdate BETWEEN '20170801' AND '20170919'