SQL Calculating day away from birthday using their DOB

Question

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

Your code assumes the `birthday` is 'this' year. Do you already have a process that updates those dates each time they have a birthday? *(So Fred might be born `1998-02-28`, but the `birthday` column would have `2022-02-28`?)* If so, when does that `birthday` get updated? *(At the beginning of the day of their birthday, at the end of the day of their birthday, at the end of the week of their birthday)*? Or, is `birthday` actually a `date-of-birth` columns, where the `year` part would usually be quite far in the past? — MatBailie, Sep 09 '21 at 09:03
Birthday is set at the day they were born , this column doesn't update — Kaede, Sep 09 '21 at 09:05
When you say `this week` and `this month`, ***exactly*** what do ***you*** mean? For `this week` you could mean "within in the next 7 days", or "within the previous 7 days", or that a 'working week' or 'calendar week' is considered to be Monday to Sunday inclusive, and you want to know if 'today' and the 'birthday' are in the same 'working week'.... — MatBailie, Sep 09 '21 at 09:06
Within the next x days , for a week: today is the 9th. I would like everey person that has their birthday from the 9th to the 15th. — Kaede, Sep 09 '21 at 09:09
That's more than a week, that's 8 days. And what does "month" mean? — Charlieface, Sep 09 '21 at 09:09
Does this answer your question? [Trying to calculate days to next birthday](https://stackoverflow.com/questions/8377559/trying-to-calculate-days-to-next-birthday) — Stu, Sep 09 '21 at 09:14
@Stu That's only half an answer to the specific question, particularly when dealing with "within the next month", because a month can be 28, 29, 30 or 31 days. *(It's useful, helpful, but doesn't complete the OP's needs.)* — MatBailie, Sep 09 '21 at 09:45

MatBailie · Accepted Answer · 2021-09-09T12:13:39.387

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

SQL Calculating day away from birthday using their DOB

1 Answers1