SQL for Birthday ou Aniversário

This SQL works on MYSQL since 4.0 is for sure, I don’t know about the older versions !

PROBLEM: I want to display people whose birthday today up to 2 days from today! Preferentialy using only DB functions.

SOLUTION: This is way harder than looks like !!!! First because i don’t want to figure a solution that is extremely DB dependent, still i don’t want it slow..

Using BETWEEN tends to go very wrong when we are close to the end of the month, lets say day 30/31 makes the ranges go a little crazy when you use day BETWEEN 30 and 2 ( expect bad results !)

So.. if the simple is the best…

"SELECT * FROM person WHERE
MONTH(birthdate) = MONTH( CURDATE() + INTERVAL 0 DAY ) AND DAY(birthdate) = DAY( CURDATE() + INTERVAL 0 DAY ) OR
MONTH(birthdate) = MONTH( CURDATE() + INTERVAL 1 DAY ) AND DAY(birthdate) = DAY( CURDATE() + INTERVAL 1 DAY ) OR
MONTH(birthdate) = MONTH( CURDATE() + INTERVAL 2 DAY ) AND DAY(birthdate) = DAY( CURDATE() + INTERVAL 2 DAY )"

I use this snippet on my Rails code and it works just fine:

   sql = "SELECT * FROM person WHERE MONTH(birthdate) = ..... "
   ActiveRecord::Base.establish_connection
   @people = ActiveRecord::Base.connection.execute(sql).to_a

*The code is not using a Model for performace issues only.. the query is not supposed to be very heavy, but you could always get the CURRENT month from your App (‘that would mean you are getting the time from the server and not from de DB’)

*Still this is supposed to work on any other programming language… ASP, JAVA, PHP, .NET 😉

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s