-1

I'm trying to calculate the difference between two dates in years using MySQL, but the DATEDIFF function seems to return the difference in days instead. For example, when I use the statement

SELECT DATEDIFF("2018-06-25", "2017-06-15") as years;

it returns "375" which is the difference in days rather than years.

I would like to know how I can get the difference between two dates in years using MySQL. Is there a specific function or method I should use to achieve this? I want the result to be the exact difference in years, considering any additional months or days.

  • 1
    See this article for how --> https://stackoverflow.com/questions/10765995/how-to-convert-given-number-of-days-to-years-months-and-days-in-mysql Now sure its about php , but ignore that, the MySQL command you want is in the answers. – easleyfixed Jul 14 '23 at 16:07
  • NOTE - When you say AS YEARS in your example, you are just giving the variable a name, that is NOT a command to make it automatically convert into years. – easleyfixed Jul 14 '23 at 16:15
  • I understand that 'years' is just a variable name. I have seen the question you provided earlier, but I couldn't find a satisfactory answer because I am a beginner and I want to fully understand the solution. Specifically, I am having trouble comprehending the table or method used for converting the difference in days into years. –  Jul 14 '23 at 16:19
  • 1
    I will mine the thing you need one sec. – easleyfixed Jul 14 '23 at 16:36
  • 1
    SELECT DATE_FORMAT(FROM_DAYS(DATEDIFF('2018-06-25', 2018-06-25')), '%Y-%m-%d') AS `total_days` Something like that perhaps? – easleyfixed Jul 14 '23 at 16:38
  • I tested that example and it has some sort of issue with it, but this is the concept here, just have to look into it a bit more. Hopefully someone can fix that example but this is the idea here. – easleyfixed Jul 14 '23 at 16:50
  • NOTE - I think that if it is less than a year, that it will fail because its not even a full value. Reading the notes that seems to be part of the issue. That being said the current answer seems like what you want. – easleyfixed Jul 14 '23 at 16:51
  • 1
    Thank U soo much my issue is resolved now i was just need year not months or days i will use if condition if the difference is less then 1 year I will return zero. –  Jul 14 '23 at 17:38

1 Answers1

2

What you probably want is TIMESTAMPDIFF

SELECT TIMESTAMPDIFF(YEAR, "2017-06-15", "2018-06-25");

That lets you specify YEAR, MONTH, MINUTE, etc.

EDIT: this returns time from first to the second date, so for a positive number, put the earlier date first.

Garr Godfrey
  • 8,257
  • 2
  • 25
  • 23
  • 1
    This pretty much works as he wants, however, because those dates are NOT a full year, the returned value is Negative 1 so you have to consider that. – easleyfixed Jul 14 '23 at 16:52
  • 1
    it is a full year but...yeah, I reversed the parameters. Put dates in temporal order to get a positive number – Garr Godfrey Jul 14 '23 at 17:02
  • 1
    ohh ok that makes sense, yeah I see the date update., this should be the accepted answer i think. – easleyfixed Jul 14 '23 at 17:10