I found myself needing to update a MySQL table today with a calculated date, so thought I'd blog the sql in case it saves someone else some time later.
The Scenario
You have a table that contains a date field, but you need to know the date of the Thursday following that date. In this query, the number 5 represents the 5th day of the week (sunday=1, Monday=2, etc.), so if your scenario is looking for a different day of the week, just substitute your day's number everywhere you see the number 5 occurring in mine. The number 12 in the query below is really 7+5, so again, substitute your day's number for 5 in the equation 7+5 and plug in the result where you see 12. If you're looking for Saturday (day 7), you would put a 14 in place of my 12.
The Query
dayofweek(adjusterpaydate)-5 as diff,
adddate(adjusterpaydate,if(dayofweek(adjusterpaydate)-5<=0,abs(dayofweek(adjusterpaydate)-5),12-dayofweek(adjusterpaydate))) as followingThursday
FROM `invoicepayment`
WHERE adjusterpaydate is not null
To update an existing field in the table with the calculated date of the following Thursday, I used a modified version of the above query that looks like this:
update `invoicepayment` set checkdate = adddate(adjusterpaydate,if(dayofweek(adjusterpaydate)-5<=0,abs(dayofweek(adjusterpaydate)-5),12-dayofweek(adjusterpaydate)))
where adjusterpaydate is not null
You are not logged in, so your subscription status for this entry is unknown. You can login or register here.
No comments found.