• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 589
  • Last Modified:

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


0
jblayney
Asked:
jblayney
  • 5
  • 4
1 Solution
 
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now