1
SELECT (CURRENT_DATE - P.DataDiPrestito) AS "Sisssssss"
FROM prestito P, utente U
WHERE P.id_Utente = U.ID_Utente

So I'm subtracting by the current date the value of a date from my database to see the difference between them in days.

The problem is that, when I subtract a value that's in the same month the result is correct but when it's from a month before it's not.

Today is the 15 of May, and if subract from it the same date value I get 0, if subtract from it the date value of the day before I get 1 and so on and so forth.

But when I subract from a value from from April I get the correct value plus 70. Like, if I do (15 of May - the 30 of April) I should be getting 15 but instead I get 85 and so on, and if try to use March or February I get even higher values, if try (15 of May - the 6 of February) I get 309.

What can I do?

Barmar
  • 741,623
  • 53
  • 500
  • 612
Joffo
  • 13
  • 6
  • As an explanation for the behavior you are seeing, the subtraction operator `-` (minus sign) operates on *numeric* values. MySQL is doing an implicit conversion of a DATE value into a numeric value, and doing numeric subtraction `20180515 - 20180430`. When the subtraction is done, its just subtracting numbers, the concept that the original values represented "dates" is lost. I recommend using the TIMESTAMPDIFF function with `DAY` keyword as the first argument. https://dev.mysql.com/doc/refman/5.7/en/date-and-time-functions.html#function_timestampdiff – spencer7593 May 15 '18 at 21:03

2 Answers2

3

Subtracting dates from each other doesn't work like you think it does.

If you subtract the 30th of April from the 15th of May, this is what gets calculated internally:

20180515 - 20180430 = 85

The numbers are simply being treated like decimals.

The function you are looking for is called DATEDIFF().

Fang
  • 2,199
  • 4
  • 23
  • 44
1

You should check out the DATEDIFF() function. This will do what you want, giving the day difference between two dates.

DATEDIFF(expr1,expr2)

Thomas
  • 442
  • 4
  • 11