Is there any way to get the # of months and years since a date, using the mysql DATE column?
So if I enter 1/1/2009.. I want 1 years, 3 months.. and so on.
preferably SQL, if its possible
jwzk
2010-03-17 01:31:36
+1
A:
DateDiff can return the difference in days between two dates. From there, you might have to do the math yourself. You could make a Stored Procedure with all the logic, but for the most part SQL isn't designed as a functional language.
EDIT: I found what you are looking for. Try this:
DATE_FORMAT(FROM_DAYS(DATEDIFF(NOW(), <date>)), '%c months and %Y years')
St. John Johnson
2010-03-17 14:52:59
It would be tough to extract the months from datediff() results, because months vary in the number of days they contain. Years would be close if you ignored leap year.
sheepsimulator
2010-03-17 15:08:43
A:
select mnth as months_between, mnth/12 as years_between
from
( select (year(<current date column>) - year(<older date column>))*12 +
(month(<current date column>) - month(<older date column)) as mnth
from <tables with date columns>
where <conditions to get your rows with the dates you need to compare>
)
Also cf. Anthony Mollinaro's SQL Cookbook, chapter 8 on Date Arithmetic.
sheepsimulator
2010-03-17 15:03:33
If you want to use today's date, you can substitute `NOW()` for the current date column.
Samir Talwar
2010-03-17 15:08:52
Not 100% accurate as you may encounter a situation like 2009/03/15 2009/02/16. This would not be 1 month, but the return value of your script would be.
St. John Johnson
2010-03-17 15:19:00
I think it depends on OPs business requirements, whether a comparison between the two would be 1/0. But yes, you are technically correct.
sheepsimulator
2010-03-17 15:34:12