doctrine Add_date in native query

Question

I want to select data from table using native query and DATE_ADD but always I got an errors,

this is my sql request:

 $qb = $this->_em->createNativeQuery("select  Coalesce (sum(p.nb_sms*p.nb_destination),0)  as multiplesms , sum(p.nb_fax) as nbFax, sum(p.nb_Mail) as nbMail FROM push p WHERE p.id_user=$id and DATE_ADD(p.date_send,7,'DAY') > CURRENT_DATE() and p.statut>0 order by p.date_send  Desc", $rsm);

I got an error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '7,'DAY') > CURRENT_DATE() and p.statut>0 order by p.date_send Desc' at line 1

any idea please

check this answer http://stackoverflow.com/a/25883677/2393077 — Tomasz Madeyski, Jan 08 '16 at 14:06
Possible duplicate of [How can I use DATE() in Doctrine 2 DQL?](http://stackoverflow.com/questions/25883616/how-can-i-use-date-in-doctrine-2-dql) — Tomasz Madeyski, Jan 08 '16 at 14:22
did you register both ``date_add`` and ``current_date`` functions? — Tomasz Madeyski, Jan 08 '16 at 14:41
`orm: dql: datetime_functions: date: DoctrineExtensions\Query\Mysql\Date date_format: DoctrineExtensions\Query\Mysql\DateFormat dateadd: DoctrineExtensions\Query\Mysql\DateAdd` — Majdi Taleb, Jan 08 '16 at 14:52

score 2 · Accepted Answer · answered Jan 10 '16 at 09:27

2

mistake in DATE_ADD function syntax
change DATE_ADD(p.date_send,7,'DAY') to DATE_ADD(p.date_send, INTERVAL 7 DAY)

answered Jan 10 '16 at 09:27

Max P.

5,579
2
13
32

doctrine Add_date in native query

1 Answers1