Checking if mySQL date field is Between 2 dates

Hello,

I have a database which stores (kids_dob) date of birth as a DATE field (eg.  2001-12-31)

I have a search which is trying to check to see if kids_dob is in between 2 dates which i determine in the search page

The user will simply type in the date range as an age (eg, 4 years old - 7 years old) , i then catch those and convert then into  dates to be used for my query.


CODE TO CONVERT AGE NUMBER INTO A DATE, they echo properly as (eg 2004-02-01)


if ($startage1 != "") {
$todaymonth = date("m");
$todayday = date("d");
$todayDateYear = date("Y");

$startdate = (($todayDateYear - $startage1) -1);
$finalstartdate = $startdate . "-" . $todaymonth . "-" . $todayday;
echo $finalstartdate;
}

if ($endage1 != "") {
$todaymonth2 = date("m");
$todayday2 = date("d");
$todayDateYear2 = date("Y");

$enddate = (($todayDateYear2 - $endage1) -1);
$finalenddate = $enddate . "-" . $todaymonth2 . "-" . $todayday2;
echo $finalenddate;
}


THIS IS MY QUERY

 AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN $finalstartdate AND $finalenddate "


my query comes back with no results


LVL 1
jblayneyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ashish PatelCommented:
try this

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN '$finalstartdate' AND '$finalenddate' "
0
jblayneyAuthor Commented:
tx for responding, i tried that already, no luck

my guess is that it is not reading my 2 built dates as a date
0
Ashish PatelCommented:
okay then lets try this

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN DATE_FORMAT('$finalstartdate', '%Y-%m-%d') AND DATE_FORMAT('$finalenddate', '%Y-%m-%d') "

also take the echo of the query and paste if this doesnt work
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Ashish PatelCommented:
Or This, same as above but you likely try this

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN DATE_FORMAT('" . $finalstartdate . "', '%Y-%m-%d') AND DATE_FORMAT('" . $finalenddate . "', '%Y-%m-%d') "

also take the echo of the query and paste if this doesnt work
0
jblayneyAuthor Commented:
Hello

That seems to freeze the whole site, all i get now is the query dump on the top and the rest of the page doesn't load... it also takes about 1 minute to load

you will notice from my dump below that i have 2 inner joins and other stuff happening with this query, but i never showed it at first because that has been working for months and this date range is a new feature i am trying to add

SELECT * FROM christmas INNER JOIN kids ON christmas.christmas_child = kids.kids_id INNER JOIN caregivers_links ON caregivers_links.caregivers_links_kids = kids.kids_id INNER JOIN caregivers ON caregivers.care_id = caregivers_links.caregivers_links_caregiver WHERE care_id != 1000000 AND care_prov = 7 AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN DATE_FORMAT('2003-02-01', '%Y-%m-%d') AND DATE_FORMAT('2002-02-01', '%Y-%m-%d') AND caregivers_links_type = 1 AND kids_inactive != 1 AND care_inactive != 1 ORDER BY care_prov, care_city, kids_lname ASC


0
Ashish PatelCommented:
can we use cast, like below.

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN Cast('" . $finalstartdate . "' As DateTime) AND Cast('" . $finalenddate . "' as DateTime) "
0
jblayneyAuthor Commented:
Same problem, takes forever for page to load and it only load the query dump.

is it possible that the query was fine, it is that my date builder isn't building it as a proper date


SELECT * FROM christmas INNER JOIN kids ON christmas.christmas_child = kids.kids_id INNER JOIN caregivers_links ON caregivers_links.caregivers_links_kids = kids.kids_id INNER JOIN caregivers ON caregivers.care_id = caregivers_links.caregivers_links_caregiver WHERE care_id != 1000000 AND care_prov = 7 AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN Cast('2003-02-01' As DateTime) AND Cast('1998-02-01' as DateTime) AND caregivers_links_type = 1 AND kids_inactive != 1 AND care_inactive != 1 ORDER BY care_prov, care_city, kids_lname ASC
0
Ashish PatelCommented:
yes that is also possible. as i dont see any issues with your query. Just keep a index on care_id and this kids_dob if you can. But i guess the date builder would be the problem.
0
jblayneyAuthor Commented:
ok, i figured it out, it was the between, i had to reverse

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN $finalstartdate AND $finalenddate "

i changed to this

AND DATE_FORMAT(kids_dob, '%Y-%m-%d') BETWEEN $finalenddate AND $finalstartdate "

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.