CODEWITHSUNDEEP
mysql
jclima05
jclima05

Reputation: 162

How to get the all mondays in a month?

I want to get all the Mondays in the month of MAY 2015

(using mysql query)

OUTPUT:

MON   
04  
11  
18  
25

Upvotes: 2

Views: 3924

Answers (3)

harvey
harvey

Reputation: 2953

For reference, here's another solution - note that the last entry may be null, can be changed to another value if necessary, or wrap in a sub-select and filter on not null.

SET @date='2015-05-01';
SET @offset=7 - WeekDay(@date);

SELECT DAY(DATE_ADD(@date,INTERVAL @offset DAY)) AS 'MON'
UNION SELECT DAY(DATE_ADD(@date,INTERVAL @offset+7 DAY))
UNION SELECT DAY(DATE_ADD(@date,INTERVAL @offset+14 DAY))
UNION SELECT DAY(DATE_ADD(@date,INTERVAL @offset+21 DAY))
UNION DISTINCT SELECT IF(DAY(DATE_ADD(@date,INTERVAL @offset+28 DAY))>21,
            DAY(DATE_ADD(@date,INTERVAL @offset+28 DAY)),
            DAY(DATE_ADD(@date,INTERVAL @offset+21 DAY)))
;

SQL Fiddle: http://sqlfiddle.com/#!9/fa4ce/4

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

This query returns the two digit day value of the Mondays in a month.

This requires the "month" as a date of the first day of the month, as a value in the SELECT list of the first inline view (d0). (This inline view query could be tweaked to handle any date value within a month as the specification for a month.)

SELECT DATE_FORMAT(d0.dt + INTERVAL d1.i*6+d2.i DAY,'%d')   AS dd
 --  , d0.dt + INTERVAL d1.i*6+d2.i DAY                     AS dt
  FROM ( SELECT '2015-05-01' + INTERVAL 0 DAY AS dt 
       ) d0
 CROSS
  JOIN ( SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       ) d1
 CROSS
  JOIN ( SELECT 0 AS i UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5
       ) d2
 WHERE d0.dt + INTERVAL d1.i*6+d2.i DAY < d0.dt + INTERVAL 1 MONTH
   AND NOT WEEKDAY(d0.dt + INTERVAL d1.i*6+d2.i DAY)
 ORDER BY 1

NOTE: This requires the month to be specified only once, in the first inline view (d0). Everything else is handled in expression that reference this one value.

The WEEKDAY function returns 0 for a date value that is a Monday, so a NOT on the return from the WEEKDAY function will return TRUE for a Monday.

For a supplied date value of '2015-05-01', this returns: 

dd
--
04
11
18
25

Upvotes: 0

marcanuy
marcanuy

Reputation: 23952

select row+1  as Mon 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('2015-05-01', INTERVAL ROW DAY) between '2015-05-01' and '2015-05-31' and DAYOFWEEK(DATE_ADD('2015-05-01', INTERVAL ROW DAY))=2;

Output

+------------+
| Mon        |
+------------+
|          4 |
|         11 |
|         18 |
|         25 |
+------------+

Tweaking a bit this query

Upvotes: 1

Related Questions