CODEWITHSUNDEEP
sqldatetimesql-server-2012
gulbaek
gulbaek

Reputation: 2551

Select between two months

I'm trying to write a select query where my OrderDate is between two months. this is my code so far.

declare @FromMonth datetime
declare @ToMonth datetime
set @FromMonth = '20111201'
set @ToMonth = '20120301'

select *
from Order o
where o.OrderDate between @FromMonth and @ToMonth

This almost works, except that it also looks at the day of month, meaning in this example that it won't select all the days' in my @ToMonth

I'm using Sql Server 2012

EDIT

Just to make it more clear, I don't wanna trust my @FromMonth and @ToMonth input to know the last day in the month. The above is just a sample to illustrate my problem.

Upvotes: 2

Views: 29705

Answers (5)

Arvin
Arvin

Reputation: 1506

I am using this approach. Works well and uses only 1 where.

@startYear, @startMonth, @endYear, and @endMonth are int's that were declared beforehand.

SELECT *
FROM Order
WHERE (Year(OrderDate) * 12 + Month(OrderDate))
BETWEEN (@startYear * 12 + @startMonth)
AND (@endYear * 12 + @endMonth);

Upvotes: 0

Darshan
Darshan

Reputation: 71

WHERE MONTH(*col name*) between'01' and '05'

this will give data b/w Jan to May and col name should be DATE COLUMN.

Upvotes: 0

Nikola Markovinović
Nikola Markovinović

Reputation: 19356

First dateadd/datediff pair returns first day of month, second returns first day of next month:

where o.OrderDate >= dateadd(m, datediff (m, 0, @FromMonth), 0) 
  and o.OrderDate < dateadd(m, datediff (m, 0, @ToMonth) + 1, 0)

EDIT: changed my response according to dems' advice.

Upvotes: 2

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239814

between rarely works as well as you might hope, I usually find it better to write the range as an inclusive/exclusive pair and apply appropriate comparisons:

declare @FromMonth datetime
declare @ToMonth datetime
set @FromMonth = '20111201'
set @ToMonth = '20120401'

select *
from Order o
where o.OrderDate >= @FromMonth and o.OrderDate < @ToMonth

This will also avoid nastiness if OrderDate includes a time component.

Upvotes: 5

markblandford
markblandford

Reputation: 3193

Change your WHERE clause to something like: 

WHERE (MONTH(o.OrderDate) BETWEEN MONTH(@FromMonth) AND MONTH(@ToMonth))
AND (YEAR(o.OrderDate) BETWEEN YEAR(@FromMonth) AND YEAR(@ToMonth))

Upvotes: 1

Related Questions