How to choose one day of name from first name and date of birth?

Hey there. I would really appreciate your help in creating a query that will accomplish the following. The problem is the dynamic promotion of what we call "names" in Europe. Nameday is a tradition in many countries in Europe and Latin America, celebrating on a specific day of the year associated with one name ( ).

I have a table of all names and their corresponding date (I am storing the date in this format "1900-08-22", but we really need the month and day)

namede table:

name   date
Bob    1900-04-22
Bob    1900-09-04
Frank  1900-01-02


The trick is that there can be multiple entries for each name, and someones "nameday" is always found first after one birthday. So let's say Bob # 1 was born on August 5, his name will fall on September 4, but if we are born after September 4, his name will be April 22.

Obviously I have a table with my users

user :

id   first_name  birth_date
1    Bob         1975-08-05
2    Frank       1987-01-01


So, whatever is based on date of birth, we need to find when a given person is celebrating their "name" based on the first name and date of birth. When I need it, it's a SQL Server query that can get me names for people in my database ;;

If you think it would be easier to split the dates in the "nameday" table by: month and day, let me know, we can do that. I need to be able to find the most efficient solution for this.

Any help is greatly appreciated,



source to share

7 replies

I would recommend that you maintain your dates 1904

as 1900

it was not a leap year and did not have Feb 29th


However, here's a request with your data:

WITH    users AS
        SELECT  1 AS id, 'Bob' AS first_name, CAST('1975-08-05' AS DATETIME) AS birth_date
        UNION ALL
        SELECT  2 AS id, 'Frank' AS first_name, CAST('1987-01-01' AS DATETIME) AS birth_date
        namedays AS
        SELECT  'Bob' AS name, CAST('1900-04-22' AS DATETIME) AS date
        UNION ALL
        SELECT  'Bob' AS name, CAST('1900-09-04' AS DATETIME) AS date
        UNION ALL
        SELECT  'Frank' AS name, CAST('1900-01-02' AS DATETIME) AS date
FROM    users u
                SELECT  TOP 1 date
                FROM    namedays nd
                WHERE = u.first_name
                        AND >= DATEADD(year, 1900 - YEAR(u.birth_date), birth_date)
                ORDER BY
                SELECT  TOP 1 date
                FROM    namedays nd
                WHERE = u.first_name
                ORDER BY
                ) AS date
        ) dates


Check out this blog post for details on performance:



Here you go:

WITH "nameday_long" ("name", "date", "p_month_day") AS (
    SELECT  n."name",
            DATEPART(month, n."date") * 100 + DATEPART(day, n."date")
    FROM    "nameday" n
    SELECT  n."name",
            DATEPART(month, n."date") * 100 + DATEPART(day, n."date") + 10000
    FROM    "nameday" n

SELECT      u."id",
            n."date" AS nameday
FROM        "user" u
LEFT JOIN   "nameday_long" n
        ON  u."first_name" = n."name"
        AND n."p_month_day" = (SELECT MIN(x."p_month_day") FROM "nameday_long" x WHERE x."p_month_day" > DATEPART(month, u."birth_date") * 100 + DATEPART(day, u."birth_date"))



  • convert dates to a format that has no years and is easy to compare. I chose an integer that would be similar to representing MMDD in daytime parts of the day.
    • I would even suggest adding a (constant) computed column to the "nameday" table using this formula, but it shouldn't be heavy on computation anyway
  • extend these names by 2 years so that it is easy to find the first one if the person's name is before his birth (according to the calendar). In this trick, I have added 1000 to the MMDD numeric representation to keep it natural
  • for each person, the first name that comes after his / her birthday is chosen.
  • use the LEFT JOIN so people with cool names like "Wojo" will still show up in the results list even if they don't have a name, right? :)


This seems like a simple solution:

select, u.first_name, min(isnull(, as nameday
from    users u
        left join namedays n on u.first_name = and dateadd(year, year(u.birth_date) - 1900, > u.birth_date
        left join namedays n2 on u.first_name = and dateadd(year, year(u.birth_date) - 1899, > u.birth_date
group by, u.first_name


Explanation: Join the table with a users

table namedays

(adjust the column

in the same year as the birthday). If the date is set after the birthday, that date is used.

If not, the table users

joins the table again namedays

, this time adjusting the column date

for the year after their birthday and using the minimum date that is after their birthday.

This solution returns the correct dates as per your example data and the situations listed in the problem (Bob's birthday changed after 9/4 when Bob called 4/22).



SELECT, First_name, MIN(
FROM user
    INNER JOIN nameday ON = nameday.userid
WHERE user.birth_date < DateAdd(year, YEAR(u.birth_date) - year(,
GROUP BY, first_name




SELECT Min (name. [Date]), [user] .first_name
FROM namede
  INNER JOIN [user] at = [user] .first_name
WHERE name. [Date]> = DateAdd (year, - (DatePart (yyyy, birth_date) -1900), birth_date)
GROUP BY [user] .first_name



WITH namedays AS (
        MONTH( 'month',
        DAY( 'day'
     birthdays AS (
        UPPER(u.first_name) 'first_name',
        MONTH(u.birth_date) 'month',
        DAY(u.birth_date) 'day'
       MIN( 'name_day'
  JOIN birthdays bd ON =
  JOIN namedays nd ON nd.month >= bd.month AND >= AND = bd.firstname
GROUP BY, t.first_name




DatePart (DayOfYear) is a great way to compare dates. And who knew I would ever learn something culturally new from SO? :-)

declare @nameday table
  Name varchar(30),
  Date smalldatetime

declare @user table
  UserName varchar(30),
  Birthday smalldatetime

insert into @nameday
  select 'Bob', '1900-04-22' union
  select 'Bob', '1900-09-04' union
  select 'Frank', '1900-01-02'

insert into @user
  select 'Bob', '1975-08-05' union
  select 'Frank', '1987-01-01'

select UserName, 
       NameDayDate = Date
  from @user as u
    inner join @nameday as nd
      on nd.Name = u.UserName
      and datepart(dayofyear, nd.Date) >= datepart(dayofyear, u.Birthday)
      and not exists (select *
                        from @nameday as ndOlder
                        where ndOlder.Name = nd.Name
                          and datepart(dayofyear, ndOlder.Date) >= datepart(dayofyear, u.Birthday)
                          and ndOlder.Date < nd.Date)




All Articles