1

I am using MySQl Server. The problem is whenever i using

SELECT DATEDIFF(year, '2017/08/25', '2011/08/25') AS DateDiff`;

Statement an error is showing which goes as

ERROR 1582 (42000): Incorrect parameter count in the call to native function 'DATEDIFF'.

The DATEDIFF() when i am trying to find the diff in months or year basically other than days is not working as it should.

however if i use SELECT DATEDIFF('2017/08/25', '2011/08/25') AS DateDiff; its returning the value in days.

Pallabi Guha
  • 25
  • 8
  • MySQL date format is **yyyy-mm-dd** not **yyyy/mm/dd** – Madhur Bhaiya Aug 24 '19 at 06:16
  • @madhurbhaiya plainly, you're mistaken – Strawberry Aug 24 '19 at 06:48
  • @Strawberry I know that MySQL does allow different formats for date/datetime types, and tries to auto-interpret non-standard format; but it recommends against using non-standard format: "It is expected that you supply valid values. Unpredictable results may occur if you use values in other formats." Ref: https://dev.mysql.com/doc/refman/8.0/en/date-and-time-types.html – Madhur Bhaiya Aug 24 '19 at 06:51
  • Nevertheless, that isn't the cause of the issue at hand. – Strawberry Aug 24 '19 at 06:53

2 Answers2

2

Datediff returns days, not years. For an approximation divide the result by 365.25.

Alternatively, take a look at How to get the difference in years from two different dates? if you're after an integer number of years or The difference in months between dates in MySQL in the second answer for advice on timestampdiff and fractional months- apply the technique to years or divide that technique by 12

Caius Jard
  • 72,509
  • 5
  • 49
  • 80
2

You are using the SQL Server syntax for DATEDIFF:

DATEDIFF(datepart, startdate, enddate)

which can return the difference in years, months, days etc.
In MySql the syntax of DATEDIFF is different:

DATEDIFF(date1, date2)

and returns the difference date1 - date2 in days.
You can use TIMESTAMPDIFF which returns the difference datetime_expr2 - datetime_expr1 in years, months, days etc as an integer:

TIMESTAMPDIFF(unit, datetime_expr1, datetime_expr2)

like this:

SELECT TIMESTAMPDIFF(year, '2011/08/25', '2017/08/25') AS DateDiff;

result:

6
forpas
  • 160,666
  • 10
  • 38
  • 76