MySQL useful dates

You get asked weird things in reporting, but the worst are translating the nice, neat, sensible dates in database tables into the strange ways that people think about dates.

Here is some MySQL SQL that returns some useful relative dates:

• start of the week
• start of last week
• start of the month
• end of previous month
• start of the previous month


select
date(@dt) base_date,
date(@dt) - interval weekday(@dt) day week_start,
date(@dt) - interval weekday(@dt) day - interval 1 week last_week_start,
date(@dt) - interval (dayofmonth(@dt)-1) day month_start,
date(@dt) - interval (dayofmonth(@dt)) day end_prev_mth,
(date(@dt) - interval (dayofmonth(@dt)-1) day) - interval 1 month start_prev_mth
from
(select @dt := now()) x