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
SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,931 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
{count} votes

3 answers

Sort by: Most helpful
  1. Naomi 7,361 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 102.4K Reputation points
    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,421 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