Tutorial :Monthly Birthday SQL Query



Question:

How would retrieve all customer's birthdays for a given month in SQL? What about MySQL? I was thinking of using the following with SQL server.

select c.name     from cust c  where  datename(m,c.birthdate) = datename(m,@suppliedDate)  order by c.name  


Solution:1

don't forget the 29th February...

SELECT c.name  FROM cust c  WHERE (      MONTH(c.birthdate) = MONTH(@suppliedDate)      AND DAY(c.birthdate) = DAY(@suppliedDate)  ) OR (      MONTH(c.birthdate) = 2 AND DAY(c.birthdate) = 29      AND MONTH(@suppliedDate) = 3 AND DAY(@suppliedDate) = 1      AND (YEAR(@suppliedDate) % 4 = 0) AND ((YEAR(@suppliedDate) % 100 != 0) OR (YEAR(@suppliedDate) % 400 = 0))  )  


Solution:2

Personally I would use DATEPART instead of DATENAME as DATENAME is open to interpretation depending on locale.


Solution:3

If you're asking for all birthdays in a given month, then you should supply the month, not a date:

SELECT c.name  FROM   cust c  WHERE  datepart(m,c.birthdate) = @SuppliedMonth  


Solution:4

I'd actually be tempted to add a birthmonth column, if you expect the list of customers to get very large. So far, the queries I've seen (including the example) will require a full table scan, as you're passing the the data column to a function and comparing that. If the table is of any size, this could take a fair amount of time since no index is going to be able to help.

So, I'd add the birthmonth column (indexed) and just do (with possible MySQLisms):

SELECT name  FROM  cust  WHERE birthmonth = MONTH(NOW())  ORDER BY name;  

Of course, it should be easy to set the birthmonth column either with a trigger or with your client code.


Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Previous
Next Post »