MYSQL QUERY to find out the every second saturday of the month of the year

Question

Hi Everyone,

I need help to write one query

Description:

I need to find out the every second Saturday of the each month of the year.

Thanks in advanced,

RPL

Answer 1

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

Answer 2

Hello riddhi, try this,

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