MySql日期相减返回月数
1.PERIOD_DIFF(P1,P2) 返回在时期P1和P2之间月数(P1和P2的格式YYMM或YYYYMM)
例：SELECT PERIOD_DIFF(DATE_FORMAT(g.VALIDITY,'%Y%m'),DATE_FORMAT(NOW(),'%Y%m')) AS r FROM 表名 g
2.分别获取日期的year和month,总month=year*12+month
SELECT DATEDIFF(g.VALIDITY,CURDATE()) AS d,
g.VALIDITY,
(YEAR(g.VALIDITY)-YEAR(CURDATE()))*12 AS Y,
MONTH(g.VALIDITY)-MONTH(CURDATE()) AS m,
(YEAR(g.VALIDITY)-YEAR(CURDATE()))*12 + MONTH(g.VALIDITY)-MONTH(CURDATE()) AS result
FROM 表名 g
WHERE (YEAR(g.VALIDITY)-YEAR(CURDATE()))*12 + MONTH(g.VALIDITY)-MONTH(CURDATE())<48;
bitsCN.com 下载本文