CODEWITHSUNDEEP
mysql
MLEN
MLEN

Reputation: 2561

Creating MySQL function using date

DELIMITER $$
CREATE FUNCTION current_age (birthdate date)
RETURNS date
BEGIN
        DECLARE age_return date;
        DECLARE rightnow date;
        SET rightnow = date(now());
        SET age_return = timestampdiff(year, rightnow, date(birthdate));
        RETURN age_return;
END  $$
DELIMITER ;

select current_age(date('2017-06-30'));

I am trying to create a function which years the difference in years. I cant get it to work and I dont understand why, because the following works fine outside the function. 

SET @birthdate = '2001-01-01';
SET @rightnow = date(now());
SELECT timestampdiff(year, @rightnow, date(@birthdate))

Upvotes: 0

Views: 24

Answers (1)

spencer7593
spencer7593

Reputation: 108530

If we want to return a number of years, then the return type would be a numeric type like INT or DECIMAL. We wouldn't return a DATE datatype.

DELIMITER $$

CREATE FUNCTION current_age(birthdate DATE)
RETURNS INT
BEGIN
  RETURN TIMESTAMPDIFF(YEAR,birthdate,DATE(NOW()));
END$$

DELIMITER ;

Upvotes: 1

Related Questions