Birthdates and looking for birthdays
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.
There's 4 Comments So Far
December 11th, 2008 at 10:44
“To improve speed, you can create computed index for this expression”Opravdu to firebird dovolí? Vždyť výraz není deterministický (řečeno MS SQL terminologií) – zkrátka obsahuje funkci current_date, čili jeho hodnota se v čase může (a bude) měnit.Honza
December 11th, 2008 at 10:53
Jasny. Bez problemu to projde. Neni problem s tou funkci. Index se jednou vytvori a je to – jasne, jedou za rok to bude chtit nechat prepocitat.
December 11th, 2008 at 14:39
Nebylo by tedy lepší normalizovat na nějaký natvrdo “domluvený rok” – nejlépe nějaký přestupný (aby se nikdo neošidil)?
December 11th, 2008 at 14:44
Ano, moznosti je mnoho. Tohle byla jen jedna z nich. Stejne tak muzu ukladat den a mesic oddelene.BTW pri prestupnem roku se to posouva na 28.2. (automaticky).
Share your thoughts, leave a comment!