0

i'm having some problem to write correctly a sql query before perform it through php file writing right code for it.

Actually i have a table sql ( tblperson ) with some person name ( as name ) and their birthday date in it ( yy/mm/dd ), i need to view persons that have made birthdays in last 15 days till today.

I have tried some different queries without success as:

SELECT name, birthday FROM tblperson 
WHERE  DATEDIFF(birthday,(SELECT DATE_SUB(NOW(), INTERVAL 15 DAY))


SELECT name, birthday  FROM tblperson 
WHERE  DATE(bb) = DATE_SUB(birthday,(NOW(), INTERVAL 15 DAY))

How can I fix it?

StandardNerd
  • 4,093
  • 9
  • 46
  • 77
Zarbat
  • 457
  • 1
  • 4
  • 14
  • duplicate question https://stackoverflow.com/questions/2490173/how-to-get-the-number-of-days-of-difference-between-two-dates-on-mysql – jetblack Sep 19 '17 at 09:39
  • You weren't far from a solution, your first query uses `DATEDIFF()`, all it needs is something to compare the result with, like `DATEDIFF(...) <= 15`. Also don't forget to swap the arguments of `DATEDIFF()` and get rid of the `DATE_SUB()`, minor things like that. – KIKO Software Sep 19 '17 at 09:40

1 Answers1

1

The birthday problem is tricky because of the year component. Here is one method that almost works:

select name, birthday
from tblperson 
where format(bb, '%m%d') <= format(curdate(), '%m%d') and
      format(bb, '%m%d') >= format(curdate() - interval 15 day, '%m%d');

The problem is the first 15 days of the year. Here is one method that handles that explicitly:

select name, birthday
from tblperson 
where (format(curdate(), '%m%d') > '0115' and
       format(bb, '%m%d') <= format(curdate(), '%m%d') and
       format(bb, '%m%d') >= format(curdate() - interval 15 day, '%m%d')
      ) or
      (format(curdate(), '%m%d') < '0115' and
       format(bb + interval 15 day, '%m%d') <= format(curdate() + interval 15 day, '%m%d') and
       format(bb + interval 15 day, '%m%d') >= format(curdate(), '%m%d')
      )
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • your firt soution works great, only i can't create a correct php script to execute it, i have tried to do $sql = "select `domain`,`expirydate`\n" . "from tbldomains\n" . "where format(`expirydate`, \'%m%d\') <= format(curdate(), \'%m%d\') and\n" . " format(`expirydate`, \'%m%d\') >= format(curdate() - interval 15 day, \'%m%d\')"; but it doesn't works, could you help me for it? Thank you so much. – Zarbat Sep 25 '17 at 13:06
  • If you use double quotes to define the string, I don't think you need to escape the single quotes. – Gordon Linoff Sep 25 '17 at 22:56