Date Difference Problem!

amycrystal123

Hi Guys,
I have been trying to work on this thing for sometime now. Pls help me out. I am using PHP and MySql.

I have a table with columns name and dob (date of birth) say the table name is "Customer".

I have a form where I can let the user enter the year values within which he/she wants to retrieve records from.
For example - fromYear and toYear (fromYear value is 20) and (toYear value is 30).

By entering those values, I want to retrieve the customers whose age is between 20 and 30.

How to do it? Please help.
Thank you.
Amy.

drew010

what format is the dob in inside the database?
mm/dd/yyyy
or yyyy/mm/dd etc...

amycrystal123

The Date format in the MySql table is YYYY-MM-DD.
Looking forward for your reply.

Thanks so much!
Amy.

drew010

$year = date("Y");

$query = "SELECT * FROM Customers WHERE substring(dob, 1, 4) > $year - $toYear AND substring(dob, 1, 4) < $year - $fromYear";

give that a try.

oops change substr to substring

amycrystal123

Hi drew010,
Thanks for your reply.
I tried using your query but it did not work. But, I figured it out !

I used this query -
$query = "SELECT * FROM CHILD WHERE
YEAR(CHILD_DO😎 > $toYear
AND
YEAR(CHILD_DO😎 < $fromYear";

It works now!
🙂

Thank you !
Amy.

tekky

$year = date("Y");
$query = "SELECT * FROM Customers WHERE ($year - year(dob)) BETWEEN $fromYear AND $toYear";

that's more likely to work.....

err, just saw the edit, regardless, this is the more clean way to do it 😉

amycrystal123

Hi,
I had mailed before with the query with which my progarm worked! I forgot to add some code to show you. Sorry !

$fromYear = date("Y") - $fromYearValue;
$toYear = date("Y") - $toYearValue;

Example - $fromYearValue = 20, $toYearValue = 30.

I used this query -
$query = "SELECT * FROM CHILD WHERE
YEAR(CHILD_DO😎 > $fromYear
AND
YEAR(CHILD_DO😎 < $toYear";

amycrystal123

Hi Tekky !

Thanks for your reply ! I tried your code and definitely it is much better and straight forward!

I tried it and it is working perfectly. Thanks a ton again!

Amy.

tekky

anytime 😉