MySQL DATE_ADD() Function

Question

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.

Can't you use CASE expression there to check the value of your column and then use DATE_ADD() based on the value — Ilyes
– Ilyes, Commented Nov 17, 2018 at 21:40

Ilyes · Accepted Answer · 2018-11-17 21:46:23Z

5

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;

answered Nov 17, 2018 at 21:46

Ilyes

15k5 gold badges31 silver badges61 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Quark Over a year ago

Thank you very much Sami. This is exactly what I was looking for, thank you very much.

Collectives™ on Stack Overflow

MySQL DATE_ADD() Function

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related