如何从数据库中获取即将到来的生日

本文关键字：即将到来 生日 获取 数据库 | 更新日期: 2023-09-27 18:22:44

我有一个数据库，我想从中获取生日在未来 15 天内的人的列表。我正在使用 ASP.NET C#和MS-Access作为数据库。我用谷歌搜索了很多，但找不到正确的解释。我正在使用 

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

此查询，它提供从今天日期开始的当前月份的生日列表。我想要的结果是即将到来的 15 天生日列表......

如何从数据库中获取即将到来的生日

试试这个。 Add current date 15天，并从中得到month。

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

Month可能会在next 15 days发生变化，因此您还需要获得该month。在这里，您将获得一个用于current month two values一个，在当前日期中添加15 days后，因此如果month已更改，您还将获得该month的值。

你可能需要一个辅助函数来为跳跃者(2 月 29 日出生的人(提供正确的帮助。诀窍是使用闰年不会失败的AddYears。

它可以创建为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;
}

然后像这样(伪 SQL(：

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

完成使用 Linq 或转换为参数化 SQL。

相关文章：