Howdy,
I have been struggeling with date/time calculations for the last couple of years and meanwhile I have quite a collection I would like to share. Note that I have avoided something like date_format(current_date,’%y-%m-01′) because I dont find that very elegant
Simple date calculations
Today
SELECT current_date
Tomorrow
SELECT current_date + interval 1 day
Yesterday (you might guess….)
SELECT current_date - interval 1 day
A week ago
SELECT current_date - interval 1 week
Rather complex date calculations
The first day of last month
SELECT last_day(current_date - interval 2 month) + interval 1 day
The last day of last month
SELECT last_day(current_date - interval 1 month)
The last day of last year
SELECT current_date - INTERVAL DAYOFYEAR(current_date) DAY
the first day of this year
SELECT current_date - INTERVAL DAYOFYEAR(current_date)-1 DAY
last monday
SELECT current_date - INTERVAL weekday(current_date) day
If you have more to add, please feel free to put them into the comments and I will happily share them here.
Cheers
Andre