SQL Calculating day away from birthday using their DOB-CodePudding

I have a question regarding finding birthdays according to the date they were born, sadly I haven't found an answer for this on this website.

I want the set a number foreach date that has X days away from being their birthday. Except i have a hard time using their DOB and datediff.

What I want:

John: 0 --Today his birthday
Eric:0
Ben:1 -- In this week
Jerry:1
Jules: 2 -- In this month
Tom: 3 -- all other dates

I have tried using DATEDIFF with format but the issue is that you can't use a format with DATEDIFF.

I tried without and this returned their birth dates.

This is the code I tried:

SELECT * 
FROM 
    (SELECT 
         [id],
         [fullname] = CONCAT(E.[name], 
                             (CASE 
                                  WHEN LEN(E.[preposition]) > 0 
                                      THEN ' '   E.[preposition] 
                              END), 
                              ', ', E.[givenname]),
         [relationnumber],
         [day] = (CASE
                      WHEN DATEDIFF(day, [birthday], '2021-09-09') < 1 
                          THEN 0
                      WHEN DATEDIFF(day, [birthday], '2021-09-09') < 8 
                          THEN 1
                      WHEN DATEDIFF(day, [birthday], '2021-09-09') < 31 
                          THEN 2
                      ELSE 3
                  END),
         [birthday]
     FROM 
         [info].[member] E
     WHERE 
         [system_active] = 1) A
ORDER BY 
    day ASC

Note: the set date '2021-09-09' I get from URL

Thanks in advance

Edit My (working) Solution

SELECT *
FROM (
    SELECT [id]
        ,[fullname] = CONCAT(E.[name], (CASE WHEN LEN(E.[preposition])>0 THEN ' ' E.[preposition] END), ', ', E.[givenname])
        ,[relationnumber] 
        ,hi = DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday]) 
        ,[day] = 
        (
            CASE
                WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) = 0 THEN 0
                WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) BETWEEN 1 AND 7 THEN 1
                WHEN DATEDIFF(day, '2021-09-09', DATEADD(year, DATEDIFF(year,[birthday], CAST('2021-09-09' as date)) , [birthday])) BETWEEN 8 AND 31 THEN 2
                ELSE 3
            END
        )
        ,[birthday]
    FROM [info].[member] E
    WHERE [system_active] = 1
    ) A
    ORDER BY day ASC

For a better answer look at MatBailie's answer. This solution just worked for me

CodePudding user response：

One of the features of DATEDIFF() is that a difference of 0 means that the values are in the same day/week/month/year.

This means that any date last year is always 1 year away from any other date in this year.

DATEDIFF(year, '2020-01-01', '2021-09-09') == 1
DATEDIFF(year, '2020-12-31', '2021-09-09') == 1

It's not counting whole years between dates. It's counting the difference of the year part only.

What this means is that I follow the following process...

Work out their next birthday...

Work out the person's birthday this year
If that was before today's date, add one more year
Which give's their next birthday (treating a birthday today as their next birthday)

Compare that to fixed date...

Is their next birthday today?
Is their next birthday before today 7days?
Is their next birthday before today 1month?

I do that as follows... (Amended to make it easier to parameterise the date this is all being calculated relative to)

SELECT
  member.*,
  next.birthday   AS next_birthday,
  CASE WHEN next.birthday  =                   today.date  THEN 0
       WHEN next.birthday <= DATEADD(DAY,   7, today.date) THEN 1
       WHEN next.birthday <= DATEADD(MONTH, 1, today.date) THEN 2
                                                                        ELSE 3
  END
      AS next_birthday_type
FROM
  member
CROSS APPLY
(
  SELECT CAST('2021-09-09' AS DATE)
)
  today(date)
CROSS APPLY
(
  SELECT DATEADD(year, DATEDIFF(year, member.birthday, today.date), member.birthday)
)
  AS this_year(birthday)
CROSS APPLY
(
  SELECT CASE WHEN this_year.birthday < today.date
              THEN DATEADD(year, 1, this_year.birthday)
              ELSE                  this_year.birthday
          END
)
  AS next(birthday)

Demos : https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=df444333bab146d4d923b210e813e852