SQL : Calculate 60 days prior to customer turns 65 Years and 3 months

Question

Hi Team - below is my query i pull ID's who are 65 Years and 3 months. but i want to pull ID's prior to 60 days before the customer turns 65Y and 3M.

for example : if the customer is turning 65Y 3 M on May 9 , 2022. they should be showing up today.
could you please help ?

Select PA.dateofbirth, id

FROM Table PA
where 1=1

AND 
        ( DATEDIFF(MONTH, CASE 
                WHEN DAY(PA.dateofbirth) > DAY(getdate())
                    THEN DATEADD(MONTH, 1, PA.dateofbirth)
                ELSE PA.dateofbirth
                END, getdate()) / 12 >= '65'
        AND DATEDIFF(MONTH, CASE 
                WHEN DAY(PA.dateofbirth) > DAY(getdate())
                    THEN DATEADD(MONTH, 1, PA.dateofbirth)
                ELSE PA.dateofbirth
                END, getdate()) % 12 >= '3'
        )
        order by 1

Answer 1

May be

select * from Table PA
where dateadd(day, 60, dateadd(month, 3, dateadd(year, 65, dateofbirth))) = cast(CURRENT_TIMESTAMP as date) -- select all customers who will be 65 years, 3 months 60 days prior to today's date

Answer 2

Two options.

1) Change getdate() to dateadd(DAY, 60, getdate()).

2) Change END, getdate()) % 12 >= '3' to END, getdate()) % 12 >= 1.

Answer 3

Hi，@kkran

Welcome to Microsoft T-SQL Q&A Forum!

I used some test data to set the date of birth to 2001-04-18, and then did some filtering operations，Please check this：

    Select dateofbirth, id  
    from pa  
    where    
    (case when  dateofbirth <= DATEADD(day,60,GETDATE())  
    then   
    DATEADD(day,-60,dateadd(month, 3, dateadd(year, 65, dateofbirth)))  
    else  
    dateofbirth  
     end  
    )=dateofbirth  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.