CODEWITHSUNDEEP
mysqlsql
Quark
Quark

Reputation: 59

MySQL DATE_ADD() Function

I am trying to update a date column based on an existing date column by adding days/weeks/months to the existing date

update Form_1 set `DateP1`= DATE_ADD(`Date`, INTERVAL `P1` week) where 
`id`=1;

This works perfectly, however what I am trying to achieve is something like this which does not seem to work

update Form_1 set `DateP1`= DATE_ADD(`Date`, INTERVAL `P1` `TimeType`) where 
`id`=1;

Here, the TimeType is another column in my table and contains values like day, week, month... basically the time information. I want to be able to pull value from the column dynamically instead of 

DATE_ADD(`Date`, INTERVAL `P1` week)

where week is static. Is there a way to achieve this. I am OK with using any other alternate method as long as I can dynamically pull the values from the TimeType table.

Upvotes: 0

Views: 463

Answers (1)

Ilyes
Ilyes

Reputation: 14928

You can use CASE expression

update Form_1 
set `DateP1`= CASE `TimeType`
                   WHEN 'day' THEN DATE_ADD(`Date`, INTERVAL `P1` day)
                   WHEN 'week' THEN DATE_ADD(`Date`, INTERVAL `P1` week)
                   ...
              END
WHERE `id`=1;

Upvotes: 5

Related Questions