CODEWITHSUNDEEP
mysql
RPL
RPL

Reputation: 79

Query to find out the second Saturday of each month of the year

I need help to write one query:

How do I find out the second Saturday of each month of the year?

Upvotes: 1

Views: 3634

Answers (2)

Karthik
Karthik

Reputation: 3271

This is workout from orbman idea, thanks orbman.

First your table is like this,

CREATE TABLE `monthdates` (
  `monthdate` date NOT NULL,
  PRIMARY KEY  (`monthdate`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

INSERT INTO `monthdates` VALUES ('2010-02-01');
INSERT INTO `monthdates` VALUES ('2010-03-01');
INSERT INTO `monthdates` VALUES ('2010-04-01');
INSERT INTO `monthdates` VALUES ('2010-05-01');
INSERT INTO `monthdates` VALUES ('2010-06-01');
INSERT INTO `monthdates` VALUES ('2010-07-01');

In this table, your providing the first date of month.

Then use this query for second saturday,

    SELECT monthdate AS first_day_of_month, DATE_ADD(monthdate, INTERVAL( ( 14 - DAYOFWEEK(monthdate) ) % 7 ) + 7 DAY) AS second_saturday_of_month, DAYNAME(DATE_ADD(monthdate, INTERVAL( ( 14 - DAYOFWEEK(monthdate) ) % 7 ) + 7 DAY)) as dayy
 FROM monthdates

Output Is :

first_day_of_month  second_saturday_of_month    dayy
2010-02-01  2010-02-13  Saturday
2010-03-01  2010-03-13  Saturday
2010-04-01  2010-04-10  Saturday
2010-05-01  2010-05-08  Saturday
2010-06-01  2010-06-12  Saturday
2010-07-01  2010-07-10  Saturday

Upvotes: 1

D'Arcy Rittich
D'Arcy Rittich

Reputation: 171371

There is a good technique demonstrating how to do this here. Scroll down to Tip #23. It will have you create a small table to facilitate the query. Once you have the table created, your query is something like this:

SELECT monthdate AS first_day_of_month,
    DATE_ADD(monthdate, 
        INTERVAL( ( 10 - DAYOFWEEK(monthdate) ) % 7 ) + 7 DAY) AS second_saturday_of_month
FROM monthdates

Upvotes: 1

Related Questions