CODEWITHSUNDEEP
sql-server
User2682
User2682

Reputation: 213

SQL Server: Find records between two dates

I need to find out the records between two dates but the tricky part is to get the records for last 5 years between the passed date. For example I am passing the start date as "12/01" and end date as "12/31" So I need the records between "December 01" and "December 31" for last 5 years. That means the records should be between Month and Date for every year. Not of all the months. I am trying the following but it is not working:

DECLARE @StartDate DATE = '12/01/2011', @EndDate DATE = '12/01/2016'
DECLARE @StartDay VARCHAR(2) = '01'
,@EndDay VARCHAR(2) = '31'
,@StartMonth VARCHAR(2) = '12' 
,@EndMonth VARCHAR(2) = '12'
,@CurrentYear VARCHAR(2) = '2016'

SELECT * FROM [Records]         
WHERE [UpdatedDate] BETWEEN @StartDate AND @EndDate
AND (DAY([UpdatedDate]) = CAST(@StartDay AS INT) OR DAY([UpdatedDate]) = CAST(@EndDay AS INT))
AND (MONTH([UpdatedDate]) = CAST(@StartMonth AS INT) OR MONTH([UpdatedDate]) = CAST(@EndMonth AS INT))
AND (YEAR([UpdatedDate]) = CAST((@CurrentYear - 5) AS INT) OR YEAR([UpdatedDate]) = CAST(@CurrentYear AS INT))

Upvotes: 0

Views: 132

Answers (2)

DatabaseCoder
DatabaseCoder

Reputation: 2032

select
    *
from
    Records
where
    (year(UpdatedDate) between @CurrentYear and (@CurrentYear + 5))
    and (month(UpdatedDate) between @StartMonth and @EndMonth)
    and (day(UpdatedDate) between @StartDay and @EndDay)

Upvotes: 0

gadgimax
gadgimax

Reputation: 11

In other words, you need only december records, for the last 5 years. You could check the month and year part of the DATE field. This should be something like year betweeen 2010 and 2015 and month equals 12.

Something like this: 

select id,name,bookyear 
 from  tab1 
 where year(bookyear) BETWEEN 2010 AND 2015;
   and month(bookyear)= 12

Hope this helps

Upvotes: 1

Related Questions