How can I use CreateFunction to calculate age based on their birthday on mySQL?

Question

I have a table with date of birth and I want to use a CreateFunction to calculate their age.

So I was thinking along the lines of extracting year from current date - Year for their date of birth (using YEAR() function as shown below)

The function below doesn't seem to work so any help would be appreciated!

DELIMITER $$
 
CREATE FUNCTION customer_age(DateOfBirth DATE)
RETURNS DATE
BEGIN
    DECLARE age DATE
    
    SET age = YEAR(curdate())- YEAR(DateOfBirth);
    
    RETURN (age);
    
    END $$
    
DELIMITER;

score 0 · Answer 1 · answered Aug 02 '21 at 21:12

You get an integer not a date,

See example

CREATE FUNCTION customer_age(DateOfBirth DATE)
RETURNS INT DETERMINISTIC
BEGIN
    DECLARE age INT;
    SET age = YEAR(curdate())- YEAR(DateOfBirth);
    
    RETURN age;
    
    END

✓

SELECT customer_age('1970-01-01')

| customer_age('1970-01-01') |
| -------------------------: |
|                         51 |

db<>fiddle here

How can I use CreateFunction to calculate age based on their birthday on mySQL?

1 Answers1