0

I'm now having trouble with using dateadd() function. Basically I want to add 1 day to cexpireday(timestamp) if it is at least 10 days after current day.

I have tried two ways but neither of them work

update card

set cexpireday = dateadd(day,1,cexpireday)

where cexpireday - current_timestamp() >= '0000-00-10 00:00:00'

and

update card

set cexpireday = date_add(cexpireday,INTERVAL 10 day )

where datediff(day,cexpireday,current_timestamp) >= 10

I got "dateadd does not exist" for the first and "incorrect parameter to navigate function datediff()" for the second.

Can anyone help me with this?

mj3475
  • 3
  • 1

2 Answers2

0

Maybe try this:

UPDATE card
SET cexpireday = DATE_ADD(cexpireday, INTERVAL 1 DAY)
WHERE cexpireday > DATE_ADD(NOW(), INTERVAL 10 DAY)
maririna
  • 51
  • 5
  • 1
    Please explain why the OP should try this - what was wrong in the initial query? What did you change, and why? – Nico Haase Mar 17 '19 at 18:24
  • why I I cannot just compare the cexpireday to current_timestamp()? – mj3475 Mar 17 '19 at 18:43
  • @mj3475 here you can find a detailed explanation for a similar problem, but in sql-server, with DATEDIFF() funciton used in where clause: https://stackoverflow.com/questions/5266643/sql-datedifference-in-a-where-clause – maririna Mar 17 '19 at 18:52
0

You need to use DATE_ADD instead of dateadd. Also you need to have a look at documentation. This is example how to use it DATE_ADD(date, INTERVAL 10 DAY). Also DATEDIFF expects 2 params start_date and end_date and returns number of days between 2 dates.

Vaghinak
  • 535
  • 6
  • 12
  • I get "truncated incorrect double value '0000-00-00 00:00:00' " update card set cexpireday = date_add(cexpireday,INTERVAL 10 day ) where cexpireday - current_timestamp() >= '0000-00-10 00:00:00' – mj3475 Mar 17 '19 at 18:36
  • you need to pass 2 params to datediff start_date and end_date in your case datediff(cexpireday, current_timestamp()) – Vaghinak Mar 17 '19 at 18:44