2

I am using DATE() function to calculate the difference between two dates in MySQL

value of SYSDATE() function is following

select SYSDATE();
2020-07-15 12:16:07.0

When I am using date from same month, it is giving correct result

select DATE(SYSDATE())- DATE('2020-07-13');
2

But when I am using date from last month it is giving difference as 86 instead of 16;

select DATE(SYSDATE())- DATE('2020-06-29');
86

Edit: I am aware that we can use DATEDIFF() but I want to verify why DATE() function is giving results like this since we are already using this in code

harv3
  • 263
  • 1
  • 6
  • 19
  • 1
    The right way is using `DATEDIFF` function. https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=44baaa01609082329a612bf3e5905ae1 – Jim Macaulay Jul 15 '20 at 07:14

1 Answers1

4

MySQL doesn't support subtracting one date from another. The code

SELECT DATE '2020-07-15' - DATE '2020-06-29';

should hence result in an error, but MySQL silently converts this to this instead:

SELECT 20200715 - 20200629;

Seeing that you want to subtract two values, it assumes that you want to work with numbers. Dates are not numbers, but their internal representation yyyymmdd can be represented numerically. So, while CAST(DATE '2020-07-15 ' AS int) fails with a syntax error, as it should, MySQL is not consistent, when it comes to subtraction. It generates the numbers 20200715 and 20200629 and works with these.

I consider this a bug. MySQL should either raise an exception or return an INTERVAL when subtracting one DATE from another.

Thorsten Kettner
  • 89,309
  • 7
  • 49
  • 73
  • So I can use this if I want to check the date difference is greater than 0 but to get the difference in days I have to use DATEDIFF()? – harv3 Jul 15 '20 at 10:48
  • Use `DATEDIFF`. Don't subtract dates in MySQL, as long as this is not supported. It can well be that in some future version subtracting dates does work. In that case the result should be an interval, but you seem to expect the number of days instead. This means that knowing that MySQL mistakenly subtracts numbers right now when subtracting dates and using this knowledge about the flaw in order to compare dates would not be future-safe. – Thorsten Kettner Jul 15 '20 at 10:54