Suppose you have a person’s date of birth and you want to calculate the last occurrence of their birthday based on today’s date e.g.
| Date of Birth | Today’s Date | Last Birthday |
| 2000-03-01 | 2013-03-01 | 2013-03-01 |
| 2000-03-01 | 2013-11-07 | 2013-03-01 |
| 2000-12-01 | 2013-11-07 | 2012-12-01 |
| 2000-11-06 | 2013-11-07 | 2013-11-06 |
| 2000-11-08 | 2013-11-07 | 2012-11-08 |
| 2004-02-29 | 2012-01-28 | 2011-02-28 |
| 2004-02-29 | 2012-02-28 | 2011-02-28 |
| 2004-02-29 | 2012-02-29 | 2012-02-29 |
| 2004-02-29 | 2012-03-01 | 2012-02-29 |
| 2004-02-29 | 2013-02-28 | 2013-02-28 |
| 2004-02-29 | 2013-03-01 | 2013-02-28 |
| 2004-02-29 | 2014-02-28 | 2014-02-28 |
| 2004-02-29 | 2014-03-01 | 2014-02-28 |
The SQL query below returns the dates in the last birthday column.
DECLARE @date_of_birth date = '2004-02-29'
DECLARE @todays_date date = '2014-03-01'
SELECT
DATEADD(YEAR,
-- Number of whole years between date of birth and today's date
CASE
-- Month of date of birth greater than month of today's date
WHEN DATEPART(MONTH, @date_of_birth) > DATEPART(MONTH, @todays_date)
THEN DATEDIFF(YEAR, @date_of_birth, @todays_date) - 1
-- Month of date of birth same as month of today's date and day of date of birth greater than day of today's date
WHEN DATEPART(MONTH, @date_of_birth) = DATEPART(MONTH, @todays_date) AND DATEPART(DAY, @date_of_birth) > DATEPART(DAY, @todays_date)
THEN
CASE
-- Date of birth is on a leap day and this year is not a leap year
WHEN DATEPART(MONTH, @date_of_birth) = 2 AND DATEPART(DAY, @date_of_birth) = 29
AND NOT (DATEPART(YEAR, @todays_date) % 400 = 0 OR (DATEPART(YEAR, @todays_date) % 100 <> 0 AND DATEPART(YEAR, @todays_date) % 4 = 0))
THEN DATEDIFF(YEAR, @date_of_birth, @todays_date)
-- Else
ELSE DATEDIFF(YEAR, @date_of_birth, @todays_date) - 1
END
-- Else
ELSE DATEDIFF(YEAR, @date_of_birth, @todays_date)
END,
@date_of_birth)
A few modifications and we have a query that calculates the next occurrence of their birthday e.g.
| Date of Birth | Today’s Date | Next Birthday |
| 2000-03-01 | 2013-03-01 | 2014-03-01 |
| 2000-03-01 | 2013-11-07 | 2014-03-01 |
| 2000-12-01 | 2013-11-07 | 2013-12-01 |
| 2000-11-06 | 2013-11-07 | 2014-11-06 |
| 2000-11-08 | 2013-11-07 | 2013-11-08 |
| 2004-02-29 | 2012-01-28 | 2012-02-29 |
| 2004-02-29 | 2012-02-28 | 2012-02-29 |
| 2004-02-29 | 2012-02-29 | 2013-02-28 |
| 2004-02-29 | 2012-03-01 | 2013-02-28 |
| 2004-02-29 | 2013-02-28 | 2014-02-28 |
| 2004-02-29 | 2013-03-01 | 2014-02-28 |
| 2004-02-29 | 2014-02-28 | 2015-02-28 |
| 2004-02-29 | 2014-03-01 | 2015-02-28 |
DECLARE @date_of_birth date = '2004-02-29'
DECLARE @todays_date date = '2014-03-01'
SELECT
DATEADD(YEAR,
-- Number of whole years between date of birth and today's date plus 1
CASE
-- Month of date of birth greater than month of today's date
WHEN DATEPART(MONTH, @date_of_birth) > DATEPART(MONTH, @todays_date)
THEN DATEDIFF(YEAR, @date_of_birth, @todays_date)
-- Month of date of birth same as month of today's date and day of date of birth greater than day of today's date
WHEN DATEPART(MONTH, @date_of_birth) = DATEPART(MONTH, @todays_date) AND DATEPART(DAY, @date_of_birth) > DATEPART(DAY, @todays_date)
THEN
CASE
-- Date of birth is on a leap day and this year is not a leap year
WHEN DATEPART(MONTH, @date_of_birth) = 2 AND DATEPART(DAY, @date_of_birth) = 29
AND NOT (DATEPART(YEAR, @todays_date) % 400 = 0 OR (DATEPART(YEAR, @todays_date) % 100 <> 0 AND DATEPART(YEAR, @todays_date) % 4 = 0))
THEN DATEDIFF(YEAR, @date_of_birth, @todays_date) + 1
-- Else
ELSE DATEDIFF(YEAR, @date_of_birth, @todays_date)
END
-- Else
ELSE DATEDIFF(YEAR, @date_of_birth, @todays_date) + 1
END,
@date_of_birth)
Ollie's Notes 