Subtract Dates

[F.Danials]

Hi

I have a database containing dates, is it possible to retrieve all dates within a specific year range, then subtract the retrieved dates from today's date?

[r937]

yes, and you can do this entirely with SQL, no php necessary

[F.Danials]

So I don't need PHP for any of it?

I want to search the DOB (DDMMYYYY) field within a database, for any users that are between the ages specified in a form (between 25 and 50)

How would I do this?

[crmalibu]

select user
     , dob
  from birthdays
 where dob between curdate() - interval 25 years and curdate() - interval 50 years

http://dev.mysql.com/doc/refman/5.0/...functions.html

[r937]

... retrieve all dates within a specific year range, then subtract the retrieved dates from today's date?

SELECT somedate
     , DATEDIFF(CURRENT_DATE,somedate) AS days_diff
  FROM daTable
 WHERE somedate >= '2005-01-01'
   AND somedate  < '2006-01-01'

the specific year range is best searched as indicated above (assuming there's an index on somedate)

this also works, but is inefficient:

WHERE YEAR(somedate) = 2005

[F.Danials]

How do I return any records contained within a database, if the value of drop down box 1 and drop down box 2 is between the difference of todays date, and the DOB stored in the database?

I hope this makes sense.

[r937]

I hope this makes sense.

nope, almost but not quite

perhaps you could show sample values from the dropdowns?

[F.Danials]

What do you mean?

drop-down menu 1 contains values 25-99
drop-down menu 2 contains values 25-99

Subtract today's date from all DOB dates within the database, in order to return the total years difference. If the total years is between drop-down 1's and drop-down 2's values, then the whole record should be displayed!

Hope this makes more sense!

[r937]

SELECT something
  FROM daTable
 WHERE DATEDIFF(CURRENT_DATE,DOB) 
       BETWEEN $dropdown1 AND $dropdown2

[F.Danials]

Do I need to replace "CURRENT_DATE" with today's date, or can I use the date() function?

SELECT something
  FROM daTable
 WHERE DATEDIFF(date(),DOB) 
       BETWEEN $dropdown1 AND $dropdown2

[r937]

Do I need to replace "CURRENT_DATE" with today's date

no, because CURRENT_DATE is today's date

MySQL's Date() function is actually the same as CURRENT_DATE

however, CURRENT_DATE is Standard SQL, so use that instead

[F.Danials]

OK. I now have the following code, but the nothing gets returned from the database.

I have a record within the database, that contains the DOB 12-05-1984, and drop-down menu 1 contains value "18", and drop-down menu 2 contains value "27".

As a result of this, the records returned, should be 12-05-1984, bu nothing gets displayed. Why is this? (Do I need to set the format of the date?)

<?php
require_once "init.php";

$from = mysql_real_escape_string($_POST['from']);
$to = mysql_real_escape_string($_POST['to']);

echo $from;
echo $to;

mysql_select_db($data, $con) or die (mysql_error());

$result = mysql_query("SELECT *
  FROM member
 WHERE DATEDIFF(CURRENT_DATE,DOB) 
       BETWEEN $from AND $to");

while($row = mysql_fetch_array($result))
{
  echo $row['username']."<br />";
}

mysql_close($con)
?>

[r937]

(Do I need to set the format of the date?)

possibly

what is the DATATYPE of the DOB column?

[r937]

please run this query and show me what you get:

SELECT DATEDIFF(CURRENT_DATE,DOB) AS d  FROM member LIMIT 9

[F.Danials]

Nothing except the contents of the two drop-down menus (from & to)

I ran the following code, including your query, but nothing was displayed.

<?php
require_once "init.php";

$from = mysql_real_escape_string($_POST['from']);
$to = mysql_real_escape_string($_POST['to']);

echo $from;
echo $to;

mysql_select_db($data, $con) or die (mysql_error());

$result = mysql_query("SELECT DATEDIFF(CURRENT_DATE,DOB) AS d  FROM member LIMIT 9");

while($row = mysql_fetch_array($result))
{
  echo $row['Username']."<br />";
}

mysql_close($con)
?>

[r937]

no, that's not what i meant

the query i asked you to run in post #15 should return only 1 column of data

please run it outside of php in a front-end app or the command line

[F.Danials]

I ran it in MySQL. The following was returned/displayed:

d
14764
11543
21596
8945
218
10232
11983
10454
9583

[r937]

do you see what this means? DATEDIFF produces the difference in days

14764 means the guy is about 40½ years old

so what would you do to change the WHERE clause to be able to use the dropdown values?

[F.Danials]

40 years old? - How did you work that out? (14764 / 365?)

I haven't a clue on what to change so that the WHERE clause includes the drop-down menus - This I defiantly need help on

[r937]

40 years old? - How did you work that out? (14764 / 365?)

you catch on quickly

I haven't a clue on what to change so that the WHERE clause includes the drop-down menus - This I defiantly need help on

WHERE DATEDIFF(CURRENT_DATE,DOB)/365 BETWEEN ...

easy, innit?

[F.Danials]

So what would the final code look like? So it only displays records between the ages set by the two drop-down menu values?

[r937]

yes, why don't you try it, again outside of php, substituting numeric literals like 9 and 37 and similar for the two dropdown values

this experimentation will familiarize you with testing queries

it should further show you that your php code will also have to ensure that the 2nd value is not less that the 1st (note: it can be equal)

[F.Danials]

So I would enter the following outside php?: - Correct?

SELECT *
FROM member
WHERE DATEDIFF(CURRENT_DATE,DOB)/365
BETWEEN 18 AND 27 

[r937]

what happened when you tested that?

[F.Danials]

Yup Bingo!

It returned just three results
Is it possible to round the difference of the two dates? - Would this be a good idea?