How to Fetch Upcoming Birthdays from Database?

Question

I am having a database, from which I want to fetch list of people whose birthdays are in coming 15 days. I'm using ASP.NET C# and MS-Access as Database. I googled a lot but Couldn't find correct explanation. I'm using

select name,category,dob 
from family_details 
where Month(dob) >= Month(NOW()) and Day(dob) > Day(Now()) 
order by dob desc

this query, which gives list of Birthdays in Current months from today's date. The result I want is the list of upcoming Birthday(say) of 15 days...

[SO Post](http://stackoverflow.com/questions/5459287/find-the-next-month-for-given-date-in-mysql) think it answers your question — MX D, Nov 20 '15 at 09:01
Possible duplicate of [SQL Select Upcoming Birthdays](http://stackoverflow.com/questions/83531/sql-select-upcoming-birthdays) — Filburt, Nov 20 '15 at 09:02
@Filburt Why SQL Server answer for an MS Access SQL question? — Fionnuala, Nov 20 '15 at 10:01

score 0 · Answer 1 · answered Nov 20 '15 at 09:49

Try this. Add 15 days in current date and get month from it.

select name,category,dob 
from family_details 
where Month(dob) in(Month(NOW()),Month(DateAdd(d,15,NOW()))) 
order by dob desc

Month can change in next 15 days so you also need to get that month .Here you will get two values one for current month and one after adding 15 days in current date so if month has changed you will get value of that month as well.

Gustav · Answer 2 · 2015-11-21T15:30:43.227

You may need a helper function to get it right also for leaplings - those born on February 29th. The trick is to use AddYears which doesn't fail for leap years.

It can be created as an extension method of DateTime:

/// <summary>
/// Calculates the next annual day of this instance of System.DateTime relative to today.
/// <para>Calculates correctly Feb. 28th as an annual day in common years for event dates of Feb. 29th.</para>
/// <para>If next annual day should be later than 9999-12-31, the annual day of year 9999 is returned.</para>
/// </summary>
/// <param name="eventDate">The date of the event.</param>
/// <returns>The date of the upcoming annual day.</returns>
public static DateTime NextAnnualDay(this DateTime eventDate)
{
    return NextAnnualDay(eventDate, DateTime.Today);
}

/// <summary>
/// Calculates the next annual day of this instance of System.DateTime relative to a future date.
/// <para>Calculates correctly Feb. 28th as an annual day in common years for event dates of Feb. 29th.</para>
/// <para>If futureDate is earlier than this instance, the value of this instance is returned.</para>
/// <para>If next annual day should be later than 9999-12-31, the annual day of year 9999 is returned.</para>
/// </summary>
/// <param name="eventDate">The date of the event.</param>
/// <param name="futureDate">The date from which to find the next annual day of this instance.</param>
/// <returns>The date of the upcoming annual day.</returns>
public static DateTime NextAnnualDay(this DateTime eventDate, DateTime futureDate)
{
    DateTime nextDate = eventDate;
    if (DateTime.MaxValue.Year - eventDate.Year == 0)
    {
        // No later next annual day can be calculated.
    }
    else
    {
        int years = futureDate.Year - eventDate.Year;
        if (years < 0)
        {
            // Don't calculate a hypothetical next annual day.
        }
        else
        {
            nextDate = eventDate.AddYears(years);
            if (nextDate.Subtract(futureDate).Days <= 0)
            {
                if (DateTime.MaxValue.Year - nextDate.Year == 0)
                {
                    // No later next annual day can be calculated.
                }
                else
                {
                    nextDate = eventDate.AddYears(years + 1);
                }
            }
        }
    }
    return nextDate;
}

Then go like this (pseudo SQL):

select name, category, dob 
from family_details 
where dob.NextAnnualDay() between today and today.AddDays(15) 
order by dob desc

Finish using Linq or transform to parameterized SQL.

How to Fetch Upcoming Birthdays from Database?

2 Answers2