CODEWITHSUNDEEP
mysqldatetimesql-update
Frankie
Frankie

Reputation: 499

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

Upvotes: 0

Views: 163

Answers (1)

Cavaz
Cavaz

Reputation: 3119

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.

Upvotes: 1

Related Questions