Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
3.00/5 (1 vote)
I am using sql query to sort Date of Births as to find upcoming birthdays. The problem is that it is considering all three i.e, Date, Month, Year while I only need to sort using Date and Month so that I can have the list of Birthdays for Tomorrow, day after tomorrow or so...
Thanks in advance...
Posted

Have a look here: http://www.mombu.com/microsoft/sql-server/t-calculate-days-till-next-birthday-336649.html[^]
The SQL code there returns the number of days until the next birthday, which should be simple for you to modify for your SQL.
 
Share this answer
 
Comments
Member 12133159 18-Nov-15 5:24am    
Thanks a lot it is working fine in SQL Server...
Another thing i want to ask is that, can it also run on MS Access as I also have to install this application in my server and there I can only work with MS Access...
OriginalGriff 18-Nov-15 5:38am    
Sorry - you'd have to try it in Access yourself - I don't use it if I can possibly avoid it.
But if it's in your C# app, then I'd be tempted to do it there instead of in SQL - you have a much better set of tools to work with!
Gustav Brock 18-Nov-15 6:30am    
That function believes all years contain 365 days which is not the case for leap years.
Here is a function for exactly this:
VB
Public Function DateNextBirthday( _
    ByVal BirthDate As Date, _
    Optional ByVal SomeDate As Variant) _
    As Date
    
    ' Calculates next birthday following SomeDate based on BirthDate.
    ' If SomeDate is earlier than BirthDate, BirthDate is returned.
    ' If next birthday should be later than 9999-12-31, the
    ' birthday of year 9999 is returned.
    '
    ' 2003-02-14. Cactus Data ApS, CPH.
    ' 2015-11-21. Corrected calculation for leap years if BirthDate is Feb. 29th.
    '             Variables renamed.
    '             Added check for excessive values.
    
    Const MaxDateValue  As Date = #12/31/9999#
    
    Dim NextBirthDate   As Date
    Dim Years           As Integer
    
    If Not IsDate(SomeDate) Then
        ' Empty or invalid parameter SomeDate.
        ' Use today as SomeDate.
        SomeDate = Date
    End If
    
    NextBirthDate = BirthDate
    If DateDiff("yyyy", BirthDate, MaxDateValue) = 0 Then
        ' No later birthday can be calculated.
    Else
        Years = DateDiff("yyyy", BirthDate, SomeDate)
        If Years < 0 Then
            ' Don't calculate hypothetical birthdays.
        Else
            NextBirthDate = DateAdd("yyyy", Years, BirthDate)
            If DateDiff("d", SomeDate, NextBirthDate) <= 0 Then
                ' Next birthday falls earlier in the year than SomeDate.
                If DateDiff("yyyy", NextBirthDate, MaxDateValue) = 0 Then
                    ' No later birthday can be calculated.
                Else
                    NextBirthDate = DateAdd("yyyy", Years + 1, BirthDate)
                End If
            End If
        End If
    End If
    
    DateNextBirthday = NextBirthDate
  
End Function

So, to run a query in Access, you would do something like this:
SQL
Select
    Id, 
    FirstName,
    LastName,
    DateNextBirthday([DOB]) As NextBirthday
From
    tblYourPersonTable
Order By
    DateNextBirthday([DOB])

Please note that this will return the correct birthday also for leaplings (those born on February 29th) for any year.
 
Share this answer
 
v3
Hi,
Try Below Query

SQL
select FirstName,LastName,DateofBirth from TableName A
where Month(DateofBirth)>=Month(GETDATE()) and Day(DateofBirth)>Day(GETDATE())
order by DateofBirth
 
Share this answer
 
Comments
Member 12133159 20-Nov-15 5:37am    
Thanx Sanjay this code is working only for the birthdays of current months...
can you please suggest me, How to fetch birthdays of upcoming 15 days from the date I fetch records.
Member 12133159 20-Nov-15 5:45am    
elect name,category,dob from family_details where Month(dob)>=Month(NOW()) and Day(dob)>Day(Now()) order by dob desc

This is the code I'm working on...
SQL
declare @tab table(name varchar(10), dob date)
insert into @tab values
('Ajay', '11/25/1990'), ('Vijay', '11/23/1995'), ('Sanjay', '11/27/1990'), ('Sanjay', '11/20/1993')

select	name,  datediff(d, getdate(), dateadd(year, datediff(year, dob, getdate()), dob)) as [Upcoming days]
from	@tab
where	datediff(d, getdate(), dateadd(year, datediff(year, dob, getdate()), dob)) between 1 and 7
 
Share this answer
 
I Got the Correct answer that is:

SQL
select name,category,marriage_anniversary_date  from family_details WHERE (DATEDIFF(dd, getdate(), DATEADD(yyyy, DATEDIFF(yyyy, dob, getdate()) + 1, dob))) % 366 <= 10



This code will display a list of Birthdays coming in 10 days either it is leap year or not it works for all Just replace 10 by the days you want...
 
Share this answer
 
Hi,
You can use below query

SQL
select ColName1,ColName2 from TableName A
where DATEDIFF(d,Getdate(),a.Birthdate)> 0
order by Birthdate desc
 
Share this answer
 
Comments
Gustav Brock 18-Nov-15 6:28am    
This will never work - a birthdate is always back in time.
sanjay243657 18-Nov-15 6:38am    
Agree..
i have added new solution Please check it will work
Member 12133159 20-Nov-15 5:44am    
Error: No values Given for 1 or more required Parameter

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900