Figuring out your next birthday (SQL)

the_moss

So I am trying to figure out in SQL (SQL Server 2005) how to get the date of your next birthday given your birthdate. I have some SQL code that works, but it is ugly, can anyone see a way to clean this up? Or a better way to do it (SQL is not my strong point).

Thanks

DECLARE @BirthDate DATETIME

DECLARE @BirthDate DATETIME

SET @BirthDate = '19810409'

SELECT dateAdd(year, convert(int, Year(GetDate() - @Birthdate) - 1899), @Birthdate)

lpope187

Your SQL works, except for the corner case of Feb. 29th. It'll return the next year where there IS an actual Feb 29th, which may or may not be what you want. The SQL below, will back date Feb 29th to Feb 28th on years where there is none. I think that is what is normal.

-- SQL to create function

CREATE FUNCTION NextBirthDay
(
 @BirthDay DateTime
)
RETURNS DateTime
AS
BEGIN
 DECLARE @CurrentAge int
 DECLARE @NextBirthDay DateTime

 SET @CurrentAge = DateDiff(year, @BirthDay, GETDATE())

 SET @NextBirthDay = DateAdd(year,@CurrentAge,@Birthday)

 IF @NextBirthDay < GetDate()
 SET @NextBirthDay = DateAdd(year,1,@NextBirthDay)

 RETURN @NextBirthDay

END

-- SQL To Test

DECLARE @BirthDay DateTime

SET @BirthDay = '1976-02-29'

DECLARE @NextBirthDay DateTime

SET @NextBirthDay = Scratch.dbo.NextBirthDay(@BirthDay)

SELECT @NextBirthDay

the_moss

Thank you for the response.

Using your test case '1976-02-29' in the code I originally posted gives me a results of '2007-02-28 00:00:00.000' which I think is correct (this is SQL Server 2005 if that makes a difference). Thank you though I hadn't considered Feb-29th.

I just really hate how my code looks. So I'll probably go to yours just because it is more readable.

Thanks again!!!

lpope187

I'm glad I could help. The inaccuracy of Feb 29th was probably my bad. It was probably a test case from one of my intermediate steps and not a result of your original function.

vr602

Hi there

Nice little challenge - I like it. Here is my effort if you're interested ( Sybase T-SQL ). Could convert to stored procedure easily.

declare @bd datetime, @bdmmdd int, @currmmdd int
select @bd = '29 feb 1964'
select @bdmmdd = datepart(mm,@bd )*100 + datepart(dd,@bd )
select @currmmdd = datepart(mm,getdate())*100 + datepart(dd,getdate())

select convert(datetime, convert(varchar,
(case when @currmmdd > @bdmmdd then 1 else 0 end + datepart (yy, getdate()) )* 10000 +
case when @bdmmdd !=229 then @bdmmdd else 228 end ))

Rgds

Angstrom

Postgres 8.1, in one SQL statement (made into a function because it uses the argument four places), correctly handles Feb 29th. Output:

pho=> select birthday ('1980-04-09');
birthday
------------
2007-04-09
(1 row)

pho=> select birthday ('1980-02-29');
birthday
------------
2007-02-28
(1 row)

pho=> select birthday ('1981-02-29');
ERROR: date/time field value out of range: "1981-02-29"

(Makes sense, 1981 wasn't a leap year.)

pho=> select birthday ('1981-12-29');
birthday
------------
2006-12-29
(1 row)

The query:

create or replace function birthday (date) returns date as $$
select case
when this_year.bd < current_date then next_year.bd
else this_year.bd
end as birthday
from
(select date ($1 -
(extract (year from $1) - extract (year from current_date)) * interval '1 year') as bd) this_year,
(select date ($1 -
(extract (year from $1) - extract (year from current_date) - 1) * interval '1 year') as bd) next_year;
$$ language sql stable strict;

Angstrom

A more declarative version:

create or replace function birthday (date) returns date as $$
select
min(bd)
from
(select date ($1 -
(extract (year from $1) - extract (year from current_date)) * interval '1 year') as bd
union all
select date ($1 -
(extract (year from $1) - extract (year from current_date) - 1) * interval '1 year') as bd) dates
where
bd > current_date
$$ language sql stable strict;

whalemangler

Why do this in SQL? This is a job for code. SQL is there to store data.

brendan

wouldn't it be more simpler to do (this works in mysql but, not 100% sure that it will work with MsSQL. Where's the online documentation for SQL server?) :

CREATE FUNCTION NextBirthDay(BirthDate Date) RETURNS Date

BEGIN

RETURN DATE_ADD(BirthDate, INTERVAL 1 YEAR);

END;

brendan

Just relised that it won't work (and can't delete it because of the time limit).

brendan

But this will

CREATE FUNCTION NextBirthDay(BirthDate Date) RETURNS Date

BEGIN

RETURN DATE_ADD(BirthDate, INTERVAL (YEAR(NOW()) - YEAR(BirthDate) + 1) YEAR);

END;

the_moss

SQL is also used to retrieve relevant data from the database.

This is part of a bigger where clause, the proc returns a list of people who's birthdays are coming up in the next X days.

Thanks everyone for the responses!!

vr602

@whalemangler said:

Why do this in SQL? This is a job for code. SQL is there to store data.

This is not a very well-informed comment. Firstly SQL doesn't store anything; it is a language used to update and retrieve data from a relational database. Secondly, those who think that data should only ever be retrieved from a database as a raw block and processed in some Java middle-tier or front end are missing out on the real power of an RDBMS. Where do I start explaining...?

PJH

@vr602 said:

This is not a very well-informed comment.

Why didn't you reply to this thread just after you first joined instead of waiting over a year?

MasterPlanSoftware

@PJH said:

@vr602 said:
This is not a very well-informed comment.
Why didn't you reply to this thread just after you first joined instead of waiting over a year?

Oh come on! These well informed replies take YEARS to come up with!

Give the guy a break!

ammoQ

@vr602 said:

Secondly, those who think that data should only ever be retrieved from a database as a raw block and processed in some Java middle-tier or front end are missing out on the real power of an RDBMS.

You obviously hate hardware vendors. Just imagine the lost sales of all those nice überservers, if people actually start using databass as they are meant to be used, instead of treating them like a fancy file system and reinventing the database functionality in the middle layer.

vr602

Well I don't hate anyone really. But, like you I suspect, I do pity middle-layer Bean-type thinkers, who never get beyond "select * from table", then complain about performance, locking contention issues, result-set joins, cache syncronisation problems blah blah.

bfair

I just had to add my two cents...

DECLARE @birthDate datetime

DECLARE @currentDate datetime

DECLARE @yearDiff int

DECLARE @calcDate datetime

SELECT @birthDate = '02-29-1976' -- '06-11-1979'

SELECT @currentDate = GETDATE()

SELECT @yearDiff = YEAR(@currentDate) - YEAR(@birthDate)

SELECT @calcDate = DATEADD(year, @yearDiff, @birthDate)

-- we'll have the birthday in the current year, if it's already passed then we'll add a year and use next year's date

IF @calcDate < @currentDate

BEGIN

SELECT @calcDate = DATEADD(year, 1, @calcDate)

END

SELECT @calcDate

mrrooster

I know it's a bit late, but here's my attempt (Sybase T/SQL):

SELECT DATEADD(year,1+DATEDIFF(month,'19780417',GETDATE())/12,'19780417')

Returns feb 29s as 28th of the next year if it's not a leap year. (Didn't try it out extensivly though.)