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

Answer 1

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

Answer 2

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))
)

Answer 3

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.

Answer 4

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