CODEWITHSUNDEEP
sqlsql-server
BlackCat
BlackCat

Reputation: 2044

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?

Upvotes: 0

Views: 155

Answers (6)

Baljeet
Baljeet

Reputation: 438

Please use single quotes around the dates 

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

Upvotes: 0

Moptan
Moptan

Reputation: 336

This should also work:

select orderid
from orders
where orderdate >= '2016-06-01'
and orderdate < '2016-07-01'

Upvotes: 1

Clar Cleetus
Clar Cleetus

Reputation: 275

 Select orderid from  Orders where DATEADD(day, -1, convert(date, 
orderdate)) between DATEADD(day, -1, convert(date, 2016-06-01)) and 
DATEADD(day, -1, convert(date, 2016-06-30))

Upvotes: 1

rcs
rcs

Reputation: 7197

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'

Upvotes: 1

Onkel Toob
Onkel Toob

Reputation: 2212

BETWEEN does not work with int only, you can use it for date columns, too. Try the following:

SELECT 
    [orderid]
FROM [Orders]
WHERE [orderdate] BETWEEN '2016-06-01' AND '2016-06-30'

Or you can make use of the YEAR and MONTH functions:

SELECT 
    [orderid]
FROM [Orders]
WHERE YEAR([orderdate]) = 2016
      AND MONTH([orderdate]) = 6

This approach can, however, result in performance problems, as there are no indexes to be used.

Upvotes: 0

BytesOfMetal
BytesOfMetal

Reputation: 714

You can use the function YEAR() to extract the year from a given date and MONTH() for the month.

SELECT orderid FROM Orders 
WHERE YEAR(orderdate) = 2016 and MONTH(orderdate) = 6

Upvotes: 1

Related Questions