Question: Calculate A date and B date according to month return A few years, less than A year is counted as A year

MySQL > select * from TIMESTAMPDIFF where TIMESTAMPDIFF + MOUTH = 4

Solution: Compare the number of days between the two dates. If the number of days is less than one month, add one

Database:

The SQL statement:

SELECT (case WHEN (' b '. 'LASTINSPECTTYPE' = 'inspectType') THEN (case WHEN ((timestampdiff(MONTH, date_format( `b`.`remouldinspectdate`, '%Y-%m-%d' ), curdate()) mod 12 ) = 0 ) THEN ( case when (date_format(curdate(),'%e')! =date_format(b.remouldinspectdate,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) ) ELSE ceiling(  (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) ) END ) ELSE ceiling( (timestampdiff(MONTH,date_format(b.remouldinspectdate,'%Y-%m-%d'),curdate()) / 12 ) ) END ) WHEN ( `b`.`LASTINSPECTTYPE` If (timestampdiff(MONTH, date_format(' b '. 'INSTALLINSPECTDATE', '%Y-%m-%d') THEN (CASE WHEN (date_format(' b '. 'INSTALLINSPECTDATE', '%Y-%m-%d') curdate()) mod 12 ) = 0 ) THEN ( case when (date_format(curdate(),'%e')! =date_format(b.INSTALLINSPECTDATE,'%e') ) THEN ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),(curdate() + INTERVAL 1 YEAR )) / 12 ) ) ELSE ceiling(  (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) ) END ) ELSE ceiling( (timestampdiff(MONTH,date_format(b.INSTALLINSPECTDATE,'%Y-%m-%d'),curdate()) / 12 ) ) END ) ELSE '' END ) AS `USEYEARS` from helloelv bCopy the code

Return result:

Problem solving