MySql日期相減返回月數

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;