SELECT All Birthdays that span 2 years (2012-2013)

Question

Just curious if someone can give me some guidance with this issue of finding birthdates with MySQL.

I currently have a 'calendar' that is setup, it shows 1 month + 3 days before and 3 days after that month.

I have run into an issue which is currently come up because the 'year' isn't the same on the last 3 days, as it will be 2013. So my code to select from MySQL is this

SELECT * FROM contacts WHERE userId = '23' AND Month(dob) IN('12', '01', '01')

And now I need to get a slightly better date comparison I think. My code is that I have 2 dates (2012-12-01) - (2013-01-30)

I know stuff like this has been asked, but I haven't been able to find anything 'decent'... if possible you could help me out that would be greatly appreciated.

In short, I think I just need a better way to 'query' my dates.... thanks in advance!

It wasn't you, Justin. It was some stranger. There were actually two downvotes. SO is strange sometimes. — O. Jones, Dec 15 '12 at 17:14

O. Jones · Answer 1 · 2012-12-15T02:44:34.023

You can query dates in a range like this:

 SELECT * 
   FROM contacts
  WHERE dob >= :first_date - INTERVAL 3 DAY
    AND dob < :first_date + INTERVAL 1 MONTH + INTERVAL 3 DAY + INTERVAL 1 DAY

There's a little bit of monkey business in the

   dob < (something)

line in the adding of an extra day, and using < instead of <=. That's because DATETIME values don't necessary have midnight in their time. So we want to include everything before midnight on the next day.

Other than that, this query finds all the rows with a date in the specified range.

If you're working with the present date, as Algomorph mentioned, you can truncate the present date to the first day of the present month like this.

  CAST(DATE_FORMAT(CURRENT_DATE,'%Y-%c-01') AS DATE)

That gets you a query like this.

 SELECT * 
   FROM contacts
  WHERE dob >= CAST(DATE_FORMAT(CURRENT_DATE,'%Y-%c-01') AS DATETIME) 
                          - INTERVAL 3 DAY
    AND dob < CAST(DATE_FORMAT(CURRENT_DATE,'%Y-%c-01') AS DATETIME)
                           + INTERVAL 1 MONTH + INTERVAL 3 DAY + INTERVAL 1 DAY

I think he's trying to only keep the same frame during the whole month until the month changes, that is, during the whole of December, he wants to query everything only in December, as well as last three days of November and first three days of January. That's why I find this "first date" of the month in my query below. — Greg Kramida, Dec 15 '12 at 02:19

score 1 · Answer 2 · edited May 23 '17 at 12:29

Try something like:

SELECT * 
FROM contacts 
WHERE userId = '23' 
   AND dob BETWEEN 
     (CURRENT_DATE - INTERVAL (DAYOFMONTH(CURRENT_DATE)+3) DAY) 
     AND ((CURRENT_DATE + INTERVAL 1 MONTH) + INTERVAL 3 DAY);

Disclaimer: this came from my head, so please test syntax. NOTE: This is assuming your [dob] field is a DATE only, not DATETIME. If using DATETIME/TIMESTAMP, cast to DATE or adjust the upper and lower bounds accordingly.

SELECT All Birthdays that span 2 years (2012-2013)

2 Answers2