MySql日期相减返回月数_MySQL

bitsCN.com

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())

 

bitsCN.com