Syntax error With the help of function, how can we return the difference in Year, Month and Days between two date values?

With the help of function, how can we return the difference in Year, Month and Days between two date values?

We can create a function, which accepts the date values as its argument and returns the difference in year, month and days, as follows

mysql> CREATE FUNCTION date_difference(Date1 DATE, date2 DATE) RETURNS VARCHAR(30)
   -> RETURN CONCAT(
   -> @years := TIMESTAMPDIFF(YEAR, date1, date2),IF (@years = 1, ' year, ', ' years, '),
   -> @months := TIMESTAMPDIFF(MONTH, DATE_ADD(date1, INTERVAL @years YEAR), date2),IF (@months = 1, ' month, ', ' months, '),
   -> @days := TIMESTAMPDIFF(DAY, DATE_ADD(date1, INTERVAL @years * 12 + @months MONTH), date2),IF (@days = 1, ' day', ' days')) ;
   Query OK, 0 rows affected (0.00 sec)

Now, pass the values of dates as argument in function date_difference

mysql> Select date_difference('2015-11-16','2016-12-17') AS Difference;
+------------------------+
| Difference             |
+------------------------+
| 1 year, 1 month, 1 day |
+------------------------+
1 row in set (0.00 sec)

The above result set gives the difference between two specified dates as argument of function.

Updated on: 2020-01-29T06:37:59+05:30

111 Views

Kickstart Your Career

Get certified by completing the course

Get Started
Advertisements