Creating MySQL function using date

Question

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

cant get it to work means?

juergen d
– juergen d

2018-01-31 20:12:43 +00:00
Commented Jan 31, 2018 at 20:12 — juergen d
– juergen d, Commented Jan 31, 2018 at 20:12

spencer7593 · Accepted Answer · 2018-01-31 20:25:40Z

1

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 ;

answered Jan 31, 2018 at 20:25

spencer7593

109k15 gold badges122 silver badges148 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Creating MySQL function using date

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related