0

The following query works

SELECT
   DATE_ADD(CURRENT_TIMESTAMP(), 1, 'DAY')
FROM table;

/* */
SELECT
   DATE_ADD(CURRENT_TIMESTAMP(), SUBSTRING_INDEX(column, ' ', 1), 'DAY')
FROM table;

However, this one not:

SELECT
   DATE_ADD(CURRENT_TIMESTAMP(), 1, SUBSTRING_INDEX(column, ' ', -1))
FROM table;

Column property contains 1 MONTH which should be passed to DATE_ADD to modify the date, but the third argument of DATE_ADD SUBSTRING_INDEX(column, ' ', -1) throws the following error in Doctrine:

Notice: Undefined property: DoctrineExtensions\Query\Mysql\SubstringIndex::$value

The code above is plain SQL so here's how I have it in Doctrine:

->andWhere("createdAt > DATE_ADD(CURRENT_TIMESTAMP(), SUBSTRING_INDEX(column, ' ', 1), SUBSTRING_INDEX(column, ' ', -1))")

Does it mean it cannot be written like this?

undefinedman
  • 620
  • 1
  • 11
  • 25
  • 1
    1) In MySQL `DATE_ADD()` cannot have 3 arguments; 2) The unit in INTERVAL definition is not a data but a keyword, and won't be evaluated. – Akina Jun 22 '22 at 11:16
  • ok, I suppose I should use CASE WHEN to get this working like mentioned here https://stackoverflow.com/questions/22403945/using-date-add-with-a-column-name-as-the-interval-value – undefinedman Jun 22 '22 at 15:02

0 Answers0