查询以找出一年中每个月的第二个星期六 [英] 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?

推荐答案

这是来自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

这篇关于查询以找出一年中每个月的第二个星期六的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！