my datefield (artists.onlineDate) is yyy-mm-dd hh:mm:ss
right now I got:
-- Today
SELECT * FROM artists WHERE DATE(artists.onlineDate) = CURDATE()
-- Current Week
SELECT * FROM artists WHERE WEEK(artists.onlineDate, 3) = WEEK(CURDATE(), 3)
-- Current Month
SELECT * FROM artists WHERE MONTH(artists.onlineDate) = MONTH(CURDATE())
-- Current Year
SELECT * FROM artists WHERE YEAR(artists.onlineDate) = YEAR(CURDATE())
But what I need is exact: Yesterday, Last Week, Last Month, Last Year
I try to explain. if we got wednesday, and I use SELECT * FROM artists WHERE DATE(artists.onlineDate) = CURDATE(), then I get monday to wednesday.
I would like to have the monday to sunday of the last week. the calendar week before.
And the same for the month and year.
I thins SUB_DATE is not the right way.
Any suggestions?
解决方案
OK I found what I was looking for at:
SELECT * FROM testwoche
WHERE WEEK (datum, 3) = WEEK(current_date, 3) - 1
AND YEAR(datum) = YEAR(current_date) ORDER BY datum DESC
same for month
SELECT * FROM testwoche
WHERE month (datum) = month(current_date) - 1
AND YEAR(datum) = YEAR(current_date)
ORDER BY datum DESC
This gives back the last week from monday to sunday and the last month
Thanks everybody for helping!