CODEWITHSUNDEEP
mysqldate
mkprkr
mkprkr

Reputation: 3

MySQL strange behavior when using CURRENT_DATE minus numeric value

I encountered strange, unexpected behavior when using the CURRENT_DATE function in MySQL. Consider the following:

SELECT 
  DATE(CURRENT_DATE - 0) AS 'today',
  DATE(CURRENT_DATE - 1) AS 'yesterday',
  DATE(CURRENT_DATE - 2) AS '2 days ago',
  DATE(CURRENT_DATE - 3) AS '3 days ago'

Strangely, this returns two dates and two NULL values (when executed on 2015-04-01). The following code returns four dates, as expected:

SELECT 
  DATE_ADD(CURRENT_DATE, INTERVAL 0 DAY) AS 'today',
  DATE_ADD(CURRENT_DATE, INTERVAL -1 DAY) AS 'yesterday',
  DATE_ADD(CURRENT_DATE, INTERVAL -2 DAY) AS '2 days ago',
  DATE_ADD(CURRENT_DATE, INTERVAL -3 DAY) AS '3 days ago'

Anyone know why the former set of code returns unexpected results and the latter returns expected results?

Upvotes: 0

Views: 311

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271003

The values for date and datetime "constants" are not dates and times. They are either strings or numbers, as the documentation almost explains clearly:

Returns the current date as a value in 'YYYY-MM-DD' or YYYYMMDD format, depending on whether the function is used in a string or numeric context.

The - creates a numeric context. So, 

DATE(CURRENT_DATE - 0) --> DATE(20150401 - 1) = DATE(20150401)

That gets converted correctly. But consider:

DATE(CURRENT_DATE - 2) --> DATE(20150401 - 2) = DATE(20150399)

That is not a valid date. So it is not converted. Also, MySQL does recognize 20150400 as a date, which is why this is converted without an error.

When using MySQL, my recommendation is to use date_add() and date_sub() for all date manipulations, unless the date column is clearly typed as a date data type.

Upvotes: 1

pala_
pala_

Reputation: 9010

Because CURRENT_DATE is date, not an integer. Consider the first query. What do you mean by -1? -1 second? minute? hour? day? month? year? How is MySQL supposed to guess that? Hence the existence of the INTERVAL statements

Upvotes: 1

Related Questions