Page 1 of 1

Help with age and dob in query

Posted: Fri Mar 07, 2008 5:39 pm
by newbie2php
Hi all!

Have a few issues with a query I am trying to run on a search page.

Bqsically we have date of births in the database. We want peoeple to be able to browse people in the database of certain ages. So we pass an age range int he URL ans use the GET data to compose the query string

Here is a snippet of the code (it is all in the switch query)

Code: Select all

$start_date = (date('Y')-22).date('-m-d');
    $end_date = (date('Y')-29).date('-m-d');
    $search_string .= "AND dob >= '".$start_date."' AND dob < '".$end_date."' ";
But this will not work as the data is not numeric because of the '-' within the dob format!

I am unsure how I can get this to work?

Any ideas guys?

Thanks

Re: Help with age and dob in query

Posted: Fri Mar 07, 2008 6:31 pm
by HCBen
Look into using the CAST or CONVERT SQL function to convert the dob column to a valid date.

In your select clause, do something like this example:

Code: Select all

 
SELECT CAST(dob AS DATE) AS dateofbirth
FROM mytable
WHERE dateofbirth >= '".$start_date."' AND dateofbirth < '".$end_date."'
 
You can then use standard ("mm/dd/yyyy" or "yyyy/mm/dd") dates for your $start_date and $end_date variables.

Also, you may want to think about using the BETWEEN expression instead:

Code: Select all

 
SELECT CAST(dob AS DATE) AS dateofbirth
FROM mytable
WHERE dateofbirth BETWEEN '".$start_date."' AND '".$end_date."'
 
I may not have the syntax exactly right but this should give you the general idea of what to try. Check the documentation for your database for specifics regarding the CAST and CONVERT functions.

I hope this helps.

Prost!
-Ben