tabs ↹ over ␣ ␣ ␣ spaces

by Jiří {x2} Činčura

Birthdates and looking for birthdays

9 Dec 2008 Databases in general, Firebird

Today there was a question in db section in builder.cz forum. In essence, how to show people having birthday today or in couple of days if I have a birthdate. Well the solution I’ll show you here is in Firebird syntax, but shouldn’t be a problem to rewrite it to any other platform you like.

So the solution is like this (of course, you can find many other ways how to solve it):

select dateadd(year, datediff(year, birthdate, current_date), birthdate) from
(
select cast('1.2.1993' as date) as birthdate from rdb$database
);

OK, what’s the idea behind. First step is to get difference between these dates in year. Then, using the dateadd function to prevent some mismatch in date arithmetics (i.e. leap years), to add this difference to birthdate. Now you have it in “current year”, so it’s easy to test whether it’s between today and today + x. To improve speed, you can create computed index for this expression.

Profile Picture Jiří Činčura is an independent developer focusing on data and business layers, language constructs, parallelism and databases. Specifically Entity Framework, asynchronous and parallel programming, cloud and Azure. He's Microsoft Most Valuable Professional and you can read his articles, guides, tips and tricks at www.tabsoverspaces.com.