Reputation: 43
How i can get a list of customers, whose age will be 5 or 10 years on a given interval of two dates ?
The query for only one date is:
SELECT * FROM Customers C
WHERE DATEDIFF(MONTH,C.StartDate,@Date)=60
OR DATEDIFF(MONTH, C.StartDate,@Date)=120
Upvotes: 2
Views: 155
Reputation: 17146
You can use a query like below See live demo
DECLARE @Date1 DATE
DECLARE @Date2 DATE
SET @Date1='03-19-2017'
SET @Date2='12-19-2017'
SELECT * FROM Customers C
WHERE ( DATEADD(m, 60,C.StartDate) BETWEEN @Date1 AND @Date2 )
OR
(DATEADD(m, 120,C.StartDate) BETWEEN @Date1 AND @Date2)
Upvotes: 1
Reputation: 35623
The syntax of T-SQL's datediff function is:
DATEDIFF ( datepart , startdate , enddate )
The order of dates IS important, to get a positive integer result the earlier date should be first and the later date should be second. If you do it the other way the results will be negative numbers.
You can overcome this by using ABS()
ABS( DATEDIFF ( datepart , date1 , date2 ) )
Upvotes: 0
Reputation: 1267
You want customers where they are 5 or less at the start and 5 or greater at the end - this means that at some point in the range they were 5.
SELECT * FROM Customers C
WHERE (DATEDIFF(MONTH,C.StartDate,@StartDate)<=60 AND DATEDIFF(MONTH,C.StartDate,@EndDate) >=60)
OR (DATEDIFF(MONTH,C.StartDate,@StartDate)<=120 AND DATEDIFF(MONTH,C.StartDate,@EndDate) >=120)
Upvotes: 4