Sql Query Error when passsing date parameters

Question

    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?

Possible duplicate of SQL Server 2008 setting DateTime variable — Magnus
– Magnus, Commented Sep 19, 2017 at 11:16

marc_s · Accepted Answer · 2017-09-19 11:14:10Z

1

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'

answered Sep 19, 2017 at 11:14

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

Comments

user8608275 · Accepted Answer · 2017-09-19 11:12:41Z

0

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.

answered Sep 19, 2017 at 11:12

user8608275

Comments

Ryan Gadsdon · Accepted Answer · 2017-09-19 11:13:21Z

0

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

You also need from clause before join

answered Sep 19, 2017 at 11:13

Ryan Gadsdon

2,4068 gold badges38 silver badges62 bronze badges

Collectives™ on Stack Overflow

Sql Query Error when passsing date parameters

3 Answers 3

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related