Link to home
Start Free TrialLog in
Avatar of Gary Brett
Gary Brett

asked on

Updating Date Fields to last day of last month

Good morning, does anyone know if its possible to update a mysql date field to the last day of previous month? I can grab the new date in a select but cant figure out how to update?

SELECT
  paid,
  date_paid As 'Old PayDate',
  Last_Day(date_paid - INTERVAL 1 MONTH) AS 'New PayDate'
FROM
  tbl_invoice
WHERE
  paid = 1

Open in new window


This obviously doesnt work but if anyone knows a method would appreciate it.
UPDATE  tbl_invoice
SET     Last_Day(date_paid - INTERVAL 1 MONTH)

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Chris Stanyon
Chris Stanyon
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Gary Brett
Gary Brett

ASKER

Perfect, thank you once more Chris, of it helps anyone else this worked:

UPDATE  tbl_invoice
SET date_paid = LAST_DAY(date_paid - INTERVAL 1 MONTH)
WHERE paid= 1;