Member Avatar for omoayan

Hi,

I am building a database for an institution. However, I will love the database to intimate the operator of birthdays of those in the database using vb 2008 and sql server. My query for the sql server goes thus:

select Surname, Other_Names, Mobile_Phone from Parishioner_Adults where WHERE day(Date_of_Birth) =day((GETDATE()+15)) AND month(Date_of_Birth)=month(getdate())

But I am not getting any result when it is called. Please can anyone give me a suggestion please.

  • 3 Contributors
  • 2 Replies
  • 115 Views
  • 9 Hours Discussion Span
  • Latest Post Latest Post by poojavb

Dani AI

Generated

— the immediate problem is that the query compares the day part of GETDATE()+15 with the month part of GETDATE() (and there is a stray double WHERE in the posted SQL). That combination will usually never match. was right to point toward using DATEADD; ’s day/month range also breaks when the window spans month or year boundaries.

A robust approach is to compute the next birthday date for each row (this year or next) and then test how many days away that date is. The example below does that and works correctly across month and year boundaries:

DECLARE @DaysAhead INT = 15;

SELECT p.Surname, p.Other_Names, p.Mobile_Phone, x.NextBirthday
FROM Parishioner_Adults p
CROSS APPLY (
  SELECT DATEADD(year,
      DATEDIFF(year, p.Date_of_Birth, CAST(GETDATE() AS date))
      + CASE WHEN DATEADD(year, DATEDIFF(year, p.Date_of_Birth, CAST(GETDATE() AS date)), p.Date_of_Birth) < CAST(GETDATE() AS date) THEN 1 ELSE 0 END,
      p.Date_of_Birth) AS NextBirthday
) x
WHERE DATEDIFF(day, CAST(GETDATE() AS date), x.NextBirthday) BETWEEN 0 AND @DaysAhead
ORDER BY x.NextBirthday;

Notes and cautions:

  • Prefer DATEADD/DATEDIFF instead of adding integers to GETDATE(); see the DATEADD documentation for details (DATEADD reference).
  • Time-of-day can skew results; cast GETDATE() to date when only the day is needed.
  • Leap-day birthdays (Feb 29) will be moved by DATEADD(year, ...) to Feb 28 in non-leap years; handle differently if March 1 is desired.
  • For very large tables, per-row functions hurt indexing. Consider a computed persisted column for month/day or a calendar table and index it if performance becomes an issue.

For a reminder system, run this query daily (SQL Agent job or scheduled task) and have the application/VB.NET code read results and send alerts. This pattern avoids the day/month mismatch and handles month/year wrap correctly.

Recommended Answers

All 2 Replies

Member Avatar for ChrisPadgham

if may work for the first 14 days of the month but if it is currently 20th of June then adding 15 will give you 35th of June which not many people have as a birthday. Try adding 15 to the getdate with the dateadd function before using day function DateADD reference.

You will need to use it for both getdate calls.

Edited by ChrisPadgham
commented: I agree +0
Member Avatar for poojavb

The birthday calculation part u need it for some particular day or 15 days onwards.....

Find the below query that will show the birthdays from current day to coming 15 days

select ID, FName, MobNo, Email, BirthDate 
FROM Tablename 
where 
Day(BirthDate) between day(GETDATE()) and day(GETDATE()+15) and 
month(BirthDate) between month(GETDATE()) and month(GETDATE()+15)

Hope it helps u....

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.