Counting by months not days

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
SirChick
Forum Contributor
Posts: 125
Joined: Tue Jul 31, 2007 11:55 am

Counting by months not days

Post by SirChick »

I have a script that i want to alter to count how many months old something is rather than days, at the moment its going by days old > 6 rather than months.. this is what i got but i dont know how to alter it :

Code: Select all

<?
//get last event date and now date
$GetDate = mysql_query("SELECT DATEDIFF(NOW(), FieldUpdate) AS MonthsOld FROM countries 
					WHERE  CountryID='1'")
					or die(mysql_error());
$monthsoldrow = mysql_fetch_assoc($GetDate);

//if its been 6 months echo the secret button
If ($monthsoldrow >6){
Echo'
<input type="submit" id="Button1" name="Button1" value="Update" style="position:absolute;left:667px;top:180px;width:75px;height:24px;z-index:20">
';
}
?>
Could some one help me with this please :D thankyou.
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

Code: Select all

If ($monthsoldrow['MonthsOld'] >6)
User avatar
RobertGonzalez
Site Administrator
Posts: 14293
Joined: Tue Sep 09, 2003 6:04 pm
Location: Fremont, CA, USA

Post by RobertGonzalez »

PS Moved to PHP - Code since this was code, not SQL.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

DATEDIFF() returns number of days, not months. This is a problem for SQL to do. ;)
User avatar
califdon
Jack of Zircons
Posts: 4484
Joined: Thu Nov 09, 2006 8:30 pm
Location: California, USA

Post by califdon »

One issue is how you define "months old". Do you count it as from the day in one month to the same day in the other month? In other words, if the first date was July 31 and the second date was Feb. 28, would that be 6 or 7 months?? Or do you count it as the difference in days to the nearest month?? Or do you count it as the number of full months between two dates?? You might have to write a custom function to give you such a result.
Post Reply