Zahid Zahid - 1 year ago 70
SQL Question

Query for results based on date-time datatype

Table : Orders

Column Datatype
------ --------
orderid int
orderdate date
customerid int

We need a query which return orders placed on June 2016.

I tried

Select orderid
from Orders
where orderdate between 2016-06-01 and 2016-06-30

It gives error because between works with
only.How to do now to accomplish that?

rcs rcs
Answer Source

I think you are missing ' when specifying the date range. Also, you need to cast orderdate as DATE. The reason is because if you have data like '2016-06-30 12:34:56', BETWEEN '2016-06-01' and '2016-06-30' will not return the record.

Select orderid 
from  Orders
where CONVERT(DATE, orderdate) between '2016-06-01' and '2016-06-30'
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download