SQL to find the next birthday (There is also a VB.Net version here).

On its surface it seems like this would be a trivial task and something that Should be easy to do with SQL. And actually it is very easy but it does require a  little twist. The twist is that you have to consider where you are in the current year before you can discover when it will occur next.

If the birthday precedes the current date the next birthday will occur in the next year.

For instance if the current date is 9/10/2009 and the birthday is 9/1/2009 the next birthday will be  9/1/2010.  Any birth date after the current date will occur in the current year. It makes perfect sense when you think about.

Create Table #Employee(
    LastName VARCHAR(20)
    , FirstName VARCHAR(20)
    , BDate DateTime)
Insert Into #Employee(
    LastName
   , FirstName
   , BDate)
Values(
    'Servo'
    , 'Tom'
    , '1969-09-01')
SELECT    LastName
        , FirstName
        , BDate
        , CASE
           WHEN DATEADD(YEAR, YEAR(GETDATE())- YEAR(BDate), BDate) <= GetDate()
           THEN DATEADD(YEAR, YEAR(GETDATE())- YEAR(BDate)+1, BDate)
           ELSE DATEADD(YEAR, YEAR(GETDATE())- YEAR(BDate), BDate)
        END AS NextBirthDate
FROM #Employee
Drop Table #Employee

Results for the current date 9/10/2009: