CODEWITHSUNDEEP
sqlsql-server
Nonick
Nonick

Reputation: 43

SQL get customers by age

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

Answers (3)

DhruvJoshi
DhruvJoshi

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

Paul Maxwell
Paul Maxwell

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

DancingFool
DancingFool

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

Related Questions