MySQL – Get all sundays in a month
To get all the Sundays or Saturdays or Mondays you can use the following MySQL code
Sunday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=1
Monday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=2
Tuesday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=3
Thursday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=4
Friday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=5
Saturday
DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=6
At the last line of this mysql query.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
select DATE_ADD('2014-10-01', INTERVAL ROW DAY) as Date, row+1 as DayOfMonth from ( SELECT @row := @row + 1 as row FROM (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6) t1, (select 0 union all select 1 union all select 3 union all select 4 union all select 5 union all select 6) t2, (SELECT @row:=-1) t3 limit 31 ) b where DATE_ADD('2014-10-01', INTERVAL ROW DAY) between '2014-10-01' and '2014-10-31' and DAYOFWEEK(DATE_ADD('2014-10-01', INTERVAL ROW DAY))=1 |
Output (Sundays of the October 2014)
2014-10-05
2014-10-12
2014-10-19
2014-10-26
