Home / Questions / 9254
Accepted
dddenis
Asked: 2020-05-07 06:20:55 +0800 CST 2020-05-07 06:20:55 +0800 CST

Calculate days and months remaining for the next anniversary

  • 772

I'm trying to count how many days and months are left for the next birthday given the date of birth by calculating it with the current date, but I can't get the days correctly, only the months, and I think I'm making it too 'long'.

How can I get the remaining days to the anniversary given the date?

Code

DROP FUNCTION IF EXISTS naixement;
DELIMITER // 
CREATE FUNCTION naixement(par_data DATE) 
RETURNS VARCHAR(25)
BEGIN
    DECLARE var_messos INT DEFAULT FLOOR((1200 + DATE_FORMAT(NOW(),'%m%d') - DATE_FORMAT(par_data,'%m%d'))/100);
    DECLARE var_dies INT DEFAULT FLOOR(TIMESTAMPDIFF( DAY, par_data, NOW()) % 30.4375);
    RETURN CONCAT(var_messos,' mesos i ',var_dies,' dies');
END //
DELIMITER ;

Resolution

DECLARE var_mesos INT DEFAULT ABS(TIMESTAMPDIFF(MONTH,  DATE(CONCAT(YEAR(NOW()),'/',MONTH(par_data),'/',DAY(par_data))), 
                                                            DATE(CONCAT(YEAR(NOW()),'/',MONTH(NOW()),'/',DAY(par_data)))));

DECLARE var_dies INT DEFAULT ABS(TIMESTAMPDIFF(DAY, DATE(CONCAT(YEAR(NOW()),'/',MONTH(par_data),'/',DAY(par_data))), 
                                                        DATE(CONCAT(YEAR(NOW()),'/',MONTH(par_data),'/',DAY(NOW())))));
mysql

1 Answers

  1. Best Answer

    In MySQL you can use theDATEDIFF .

    From the documentation (free translation from English...):

    DATEDIFF()returns expr1 − expr2expressed as a value in days. expr1and expr2are expressions of type date or date-and-time . Only the date part is used in the calculation.

    mysql> SELECT DATEDIFF('2007-12-31 23:59:59','2007-12-30');
        -> 1
mysql> SELECT DATEDIFF('2010-11-30 23:59:59','2010-12-31');
        -> -31
    • 4