SQL Execute on Certain Date Query

Question

I have a small database with my website and I would like to execute a subtraction from everyone's account on a certain date like:

UPDATE Loan SET balance = (balance-amount)
"WHERE Day = 15"

Obviously the last part is just a guess but I have no due date in the loan table so sadly I can't do subtract when curDate= duedate sort of thing so I thought I would just do a set day when everyone's balance is subtracted on a set day.

Answer 1

You can get the DAY() from NOW() function and can use it in your query like

UPDATE Loan SET balance = (balance-amount)
WHERE DAY(NOW()) =15 

See fiddle to get day from NOW()

Answer 2

You have to maintain some kind of reference date in order for this to work.

If everyone pays on the same day of the month, add a table that preserves the last time you extracted the fee. You'll calculate the next fee date using the period and the last time you extracted it.

Remind me not to go to your website. Too many fees.