how to substract two dates in mysql and show the result in months?

substract two dates in mysql and show the result in months?

[shams]

hi,
i have a table pahrmacy with the columns drugname exipry and monhtsleft, the xpiry is the expiry date of drug,i want a query that substract the currnt date from the expiry date and show the result in months for the monthsleft column, any one can help please?

[StupidScript]

What format are you using for the dates? That just makes it easier or harder.

Imagining your dates are stored as "March 3, 2007":

$expires=strtotime(str_replace(",","",$row['expiry']));

$rightnow=time();

$secondsleft=$expires-$rightnow;

$monthsleft=round($secondsleft/2592000);

print("Expires in around ".$monthsleft." months.\n");

It would be easier if your dates were being stored as timestamps ...

(I say "around x months" because I just used a 30-day period and didn't take into account exactly which months are involved and their relative lengths.)

(There are 86400 seconds in a day ... 31536000 in a year.)

[shams]

hi StupidScript,
thanks for reply, sorry i forgot to write the date format it is mysql date fromat 2006-05-24, please help me to change the query to the current date format.

[shams]

hi StupidScript,
i changed the (",","",$row['expiry']) to ("",",",$row['expiry']) for my expiray date format 2007-03-01 and then rum the above script but the result was:
Expires in around -0 months. Expires in around -0 months.
which is not the correct answer any help please?

[dreamcatcher]

Hi shams,

Take a look at the MySQL date functions:
[dev.mysql.com...]

CURDATE() and DATEDIFF() are possibly what you need.

dc

[StupidScript]

In addition to reviewing the excellent link provided by dreamcatcher, there's a pretty simple fix for you since your date format is already pretty good.

Change:

$expires=strtotime(str_replace(",","",$row['expiry']));

To:

$expires=strtotime($row['expiry']);

Should be all you'd need. (There are no commas in your actual date format, and the one you're using will work fine without modification.)

I'm assuming that this code is being used during the

mysql_fetch_array()

or similar function, and that you actually have a row result named 'expiry' and that it has data in it. The code I posted will not work all by itself. It needs to be part of a larger script that gets the real data from the database. ;)

[shams]

thanks so much to all specialy StupidScript, that code works prefect.