Frankie Frankie - 1 month ago 10
MySQL Question

MySQL ADD_DATE function from datetime column

I have a dtinsert column (with old values >> DATETIME type) ad dtexpired colum (without values >> DATE type). I want update dtexpired column from the value of dtins column.

This is the SQL query to try the statement. The update query not exist:

SELECT DATE_ADD(DATE_FORMAT(dtinsert ,'%Y-%m-%d'), INTERVAL 180 DAY)
FROM usertable


When execute, the statement, the value in dataset is without adding the 180 days.

How to fix it?

Thanks

Answer

You need to update the table

UPDATE usertable 
SET dtexpired = DATE_ADD(dtinsert, INTERVAL 180 DAY)

You won't need the DATE_FORMAT since it converts a datetime into a string, thus forcing the DATE_ADD to parse it back.

Depending on the use cases, if you don't plan to update dtinserts, you may want also to add a WHERE dtexpired is NULL to avoid useless updates.

Comments