查询以找出一年中每个月的第二个星期六

问题描述：

我需要帮助来编写一个查询:

I need help to write one query:

我如何找出一年中每个月的第二个星期六?

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

答

这是来自orbman想法的锻炼，谢谢orbman.

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

输出为:

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

查询以找出一年中每个月的第二个星期六

相关推荐