Zahid Zahid - 3 months ago 9
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
int
only.How to do now to accomplish that?

rcs rcs
Answer

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'