2

I need to find the birth day of people from the table .. coming in next 7 days from today. I have a query ..SELECT * FROMtableWHEREdobLIKE BETWEEN %-08-17 AND %-08-24 but it returns the records whose dates are not submitted in database..i mean the entry is 0000-00-00 I have stored the birthdates in dates format in table. Please Help me finding the bug.

user1110597
  • 77
  • 1
  • 4
  • 10

6 Answers6

6

Since this is mysql, I don't know if DATE_FORMAT() can work on this. But give this a try.

SELECT * FROM users WHERE DATE_FORMAT(dob, '%c-%d') 
BETWEEN DATE_FORMAT('1983-08-17', '%c-%d') 
AND DATE_FORMAT('1983-08-24', '%c-%d') OR (MONTH('1983-08-17') > MONTH('1983-08-24')
AND (MONTH(dob) >= MONTH('1983-08-17') 
OR MONTH(dob) <= MONTH('1983-08-24')))

any year can be used (just to complete the date format) since year does not matter

UPDATE 1

Tested it on SQLFiddle.com

SQLFiddle Demo

UPDATE 2

I'm sorry for my first answer. I honestly missed to read this line coming in next 7 days from today. And I think that was the reason why I was downvoted by Imre L. He has his point. The reason why I posted the answer like that was because I thought the OP was asking for the days in between regardless of the year. So here is the update.

SELECT ....
FROM   ....
WHERE  DATE(dob) BETWEEN NOW() AND NOW() + INTERVAL 7 DAY

Hope it's clear now. :D

Matteus Barbosa
  • 2,409
  • 20
  • 21
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Clever. This performs about a tenth of a second better than my idea on my dataset (just a random table with a lot of records and dates). I dig it. – MetalFrog Aug 17 '12 at 14:53
  • what if current date is 2012-08-30 and +7 is 2012-09-06 likewise what if current date is 2012-12-30? – Imre L Aug 17 '12 at 14:58
  • @MetalFrog Tested it and it worked [*SQLFiddle Demo Link*](http://sqlfiddle.com/#!2/c69d8/2) – John Woo Aug 17 '12 at 15:01
  • -1 because it is totally broken on 2 cases: 1) when the year changes in next 7 days 2) when it will be october in next 7 days (its a string comparison) – Imre L Aug 17 '12 at 15:43
  • Your answer is edited answer is still wrong. Because nobody in the database can possibly have birthdays in the future =) – Max Jan 24 '14 at 11:32
1

this will handle correctly cases wen there is a month or year change between the date range: 

select *
  from people
 where (DAYOFYEAR(dob)+IF(DAYOFYEAR(CURDATE())>DAYOFYEAR(dob),1000,0)) 
       between DAYOFYEAR(CURDATE()) 
           and (DAYOFYEAR(CURDATE() + INTERVAL 7 DAY)+IF(DAYOFYEAR(CURDATE())>DAYOFYEAR(CURDATE() + INTERVAL 7 DAY),1000,0))
Imre L
  • 6,159
  • 24
  • 32
1

By converting the dob date into this year's date you can avoid issues where the period crosses a month or year boundary. This selects all rows where the birthdate occurs in the coming week:

SELECT * FROM users
WHERE concat( year(now()), mid(dob,5,6) ) 
BETWEEN now() AND date_add(now(), interval 7 day)
POM
  • 11
  • 1
0
SELECT
str_to_date(DATE_ADD(dob, INTERVAL (YEAR(CURRENT_DATE()) - YEAR(dob)) YEAR), '%Y-%m-%d') BIRTHDAY,A.*
FROM app_membership A
WHERE str_to_date(DATE_ADD(dob, INTERVAL (YEAR(CURRENT_DATE()) - YEAR(dob)) YEAR), '%Y-%m-%d') 
      BETWEEN str_to_date('15-10-2017','%d-%m-%Y') and str_to_date('10-11-2017','%d-%m-%Y')
ORDER BY BIRTHDAY ASC;

Try this. Worked for me.

Martin
  • 3,960
  • 7
  • 43
  • 43
Manoj Kumar
  • 1
0

Lets list people who born in any month/year between december 14 and august 24. We know its an year before another one. We must count with months in the previous year. It's complex because you may have a problem comparing the month of the starting date with the month of the ending date. However, it may be solved with this query:

SELECT * FROM t_users WHERE (DATE_FORMAT(d_birth, '%m-%d') 
BETWEEN DATE_FORMAT('2017-12-14', '%m-%d') AND 
DATE_FORMAT('2018-08-24', '%m-%d'))

OR(MONTH('2017-12-31') >= MONTH('2018-08-24')
AND (MONTH(d_birth) >= MONTH('2017-12-31') 
OR MONTH(d_birth) <= MONTH('2018-08-24')))
Matteus Barbosa
  • 2,409
  • 20
  • 21
0

Simple query

SELECT * FROM TEST1 WHERE DATE_FORMAT(dob, '%m-%d') BETWEEN '04-01' AND '04-30';

https://youtu.be/CaJKmw9NLq0

user7641341
  • 169
  • 1
  • 5