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

kkran 831 Reputation points
2022-03-09T23:42:22.573+00:00

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
Developer technologies | Transact-SQL
SQL Server | Other
{count} votes

3 answers

Sort by: Most helpful
  1. Naomi Nosonovsky 8,431 Reputation points
    2022-03-10T02:21:26.22+00:00

    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

    0 comments No comments

  2. Erland Sommarskog 122.3K Reputation points MVP Volunteer Moderator
    2022-03-10T22:36:12.077+00:00

    Two options.

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

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

    0 comments No comments

  3. Bert Zhou-msft 3,436 Reputation points
    2022-03-11T03:30:38.347+00:00

    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.

    0 comments No comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.