blocks|key|2091186|text|尝试使用Try+using和Try+using函数。http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek|type|unstyled|depth|inlineStyleRanges|entityRanges|offset|length|data|2091187|你可以通过操作mod+7检查从每月1号到curdate()有多少周()，并且星期应该是5(星期四)|2091188|entityMap|0|LINK|mutability|MUTABLE|url|http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek^0|Q|2E|0|0|0^^$0|@$1|2|3|4|5|6|7|N|8|@]|9|@$A|O|B|P|1|Q]]|C|$]]|$1|D|3|E|5|6|7|R|8|@]|9|@]|C|$]]|$1|F|3|-4|5|6|7|S|8|@]|9|@]|C|$]]]|G|$H|$5|I|J|K|C|$L|M]]]]

Try using dayofweek and dayofmonth functions. <a href="http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek" rel="nofollow">http://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_dayofweek</a>

Somehow you can check how many weeks are there from 1st of month to curdate() with dayofmonth (using an operation mod 7), and dayofweek should be 5 (thursday)

blocks|key|2949192|text|当前月份的第三个星期一的日期将由SQL语句给出：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2949193|SELECT+date_add(date_sub(curdate(),INTERVAL+dayofmonth(curdate())-1+DAY),+
++++INTERVAL+(7-weekday(date_sub(curdate(),INTERVAL+dayofmonth(curdate())-1+DAY)))%2B14+DAY)|code-block|syntax|javascript|2949194|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The date of the third monday of the current month would be given by the SQL statement: 

<pre><code>SELECT date_add(date_sub(curdate(),INTERVAL dayofmonth(curdate())-1 DAY), 
 INTERVAL (7-weekday(date_sub(curdate(),INTERVAL dayofmonth(curdate())-1 DAY)))+14 DAY)
</code></pre>

blocks|key|2949196|text|SELECT+
(
++DAYOFWEEK(NOW())+=+2++
++AND+
++DAYOFMONTH(NOW())+BETWEEN+15+AND+21
)+
AS+send_notice_today;|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2949197|unstyled|entityMap^0|0^^$0|@$1|2|3|4|5|6|7|G|8|@]|9|@]|A|$B|C]]|$1|D|3|-4|5|E|7|H|8|@]|9|@]|A|$]]]|F|$]]

<pre><code>SELECT 
(
 DAYOFWEEK(NOW()) = 2 
 AND 
 DAYOFMONTH(NOW()) BETWEEN 15 AND 21
) 
AS send_notice_today;
</code></pre>

blocks|key|2949247|text|我采用的方法是获得这个月的第一个星期一，根据这个月的什么时候，增加2周或3周(因为当它在周一之前/周一掉出来时，你只需要再走两周)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2949248|+;with++
+filler+as+(select+row_number()+over+(order+by+a)+a+from+(select+top+100+1+as+a+from+syscolumns)+a+cross+join+(select+top+100+1+as+b+from+syscolumns)+b),
+dates+as+(select+dateadd(month,+a-1,+'1/1/1900')+date+from+filler+where+a+<=+2000),
+FirstMonday+as+(
+select+dateadd(day,+case+++datepart(weekday,Date)
++++++++++++++++++++++++++++when+1+then+1
++++++++++++++++++++++++++++when+2+then+0
++++++++++++++++++++++++++++when+3+then+6
++++++++++++++++++++++++++++when+4+then+5
++++++++++++++++++++++++++++when+5+then+4
++++++++++++++++++++++++++++when+6+then+3
++++++++++++++++++++++++++++when+7+then+2
++++++++++++++++++++++++end,+Date)+as+Date
++++++++,case+when+datepart(weekday,Date)+=+1+then+3+else+2+end+as+Weeks
+from+++dates
+)
+select+dateadd(week,+Weeks,+Date)+as+ThirdMonday
+from+FirstMonday|code-block|syntax|javascript|2949249|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

The approach that I'm taking is get the 1st Monday of the month, and depending on when in the month it is, add either 2 or 3 weeks to it (since when it falls out before/on Monday, you only need to walk 2 more weeks):

<pre><code> ;with 
 filler as (select row_number() over (order by a) a from (select top 100 1 as a from syscolumns) a cross join (select top 100 1 as b from syscolumns) b),
 dates as (select dateadd(month, a-1, '1/1/1900') date from filler where a &lt;= 2000),
 FirstMonday as (
 select dateadd(day, case datepart(weekday,Date)
 when 1 then 1
 when 2 then 0
 when 3 then 6
 when 4 then 5
 when 5 then 4
 when 6 then 3
 when 7 then 2
 end, Date) as Date
 ,case when datepart(weekday,Date) = 1 then 3 else 2 end as Weeks
 from dates
 )
 select dateadd(week, Weeks, Date) as ThirdMonday
 from FirstMonday
</code></pre>

blocks|key|2096284|text|它计算给定年-月-日的任何月份的第一个星期一|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2096285|SET+@firstday+=+'2015-04-01';
SELECT+ADDDATE(+@firstday+,+MOD((9-DAYOFWEEK(@firstday)),7))+as+first_monday;|code-block|syntax|javascript|2096286|它计算给定年-月-日的任何月份的第三个星期一|2096287|SET+@firstday+=+'2015-01-01';
SELECT+ADDDATE(+@firstday+,+MOD((23-DAYOFWEEK(@firstday)),21))+as+third_monday;|2096288|它计算给定年-月-日的任何月份的第三个星期五|2096289|SET+@firstday+=+'2015-09-01';
SELECT+ADDDATE(+@firstday+,+MOD((20-DAYOFWEEK(@firstday)),20))+as+third_friday;|2096290|感谢@Brewal提出的原始问题和@User2208436为我们指明了答案。|2096291|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|T|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|U|8|@]|9|@]|A|$]]|$1|I|3|J|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|O|3|P|5|6|7|Y|8|@]|9|@]|A|$]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

This one calculates the first Monday of any month given the year-month-day

<pre><code>SET @firstday = '2015-04-01';
SELECT ADDDATE( @firstday , MOD((9-DAYOFWEEK(@firstday)),7)) as first_monday;
</code></pre>

This one calculates the third Monday of any month given the year-month-day

<pre><code>SET @firstday = '2015-01-01';
SELECT ADDDATE( @firstday , MOD((23-DAYOFWEEK(@firstday)),21)) as third_monday;
</code></pre>

This one calculates the third Friday of any month given the year-month-day

<pre><code>SET @firstday = '2015-09-01';
SELECT ADDDATE( @firstday , MOD((20-DAYOFWEEK(@firstday)),20)) as third_friday;
</code></pre>

Thanks to @Brewal for the original question and @User2208436 for pointing us toward the answer.

blocks|key|2091285|text|这里有一个不使用DAYOFWEEK或DAYOFMONTH的答案。它只使用DATEADD和DATEPART。|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|2091286|我们需要两个助手函数：|2091287|CREATE+FUNCTION+dbo.day_of_week(@date+Date)
RETURNS+INT
AS+BEGIN
++++--+(1+for+Sunday,+2+for+Monday,+etc)
++++RETURN+DATEPART(dw,+DATEADD(year,+year(@date)-1900,+DATEADD(month,+month(@date)-1,+DATEADD(day,+day(@date)-1,+0))))
END
GO

CREATE+FUNCTION+dbo.date_from_parts(@year+INT,+@month+INT,+@day+INT)
RETURNS+DATE
AS+BEGIN
++++RETURN+DATEADD(year,+@year-1900,+DATEADD(month,+@month-1,+DATEADD(day,+@day-1,+0)))
END
GO|code-block|syntax|javascript|2091288|然后使用以下示例数据：|2091289|DECLARE+@day_of_week+INT
SET+@day_of_week+=+2+--+Monday

DECLARE+@year+INT
DECLARE+@month+INT
SET+@year+=+2016
SET+@month+=+11|2091290|让我们首先获得这个月的第一个星期一：|2091291|我们将在这个月的第一天添加一个偏移量(day_of_week+-+day+of+week+of+first+day+of+the+month)+%25+7。|2091292|(还要注意，我们需要构造((x+%25+n)+%2B+n)+%25+n，而不仅仅是x+%25+7，以将答案保存在0和6中。例如，只有SELECT+-3+%25+7返回-3！参见Mod+negative+numbers+in+SQL+just+like+excel)|2091293|下面是获取本月第一个星期一的最终构造：|2091294|SELECT
++++DATEADD(
++++++++dd,
++++++++(((@day_of_week+-
+++++++++++dbo.day_of_week(dbo.date_from_parts(@year,+@month,+1)))+%25+7)+%2B+7)+%25+7,
++++++++dbo.date_from_parts(@year,+@month,+1)
++++)|2091295|要获得本月的第三个星期一，请在此答案的第二项中加上14。|2091296|entityMap|0|LINK|mutability|MUTABLE|url|https://stackoverflow.com/questions/10472783^0|8|9|I|A|10|7|18|8|0|0|0|0|0|0|I|1L|0|C|H|Z|5|1C|1|1E|1|1M|D|21|2|26|17|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1A|8|@$9|1B|A|1C|B|C]|$9|1D|A|1E|B|C]|$9|1F|A|1G|B|C]|$9|1H|A|1I|B|C]]|D|@]|E|$]]|$1|F|3|G|5|6|7|1J|8|@]|D|@]|E|$]]|$1|H|3|I|5|J|7|1K|8|@]|D|@]|E|$K|L]]|$1|M|3|N|5|6|7|1L|8|@]|D|@]|E|$]]|$1|O|3|P|5|J|7|1M|8|@]|D|@]|E|$K|L]]|$1|Q|3|R|5|6|7|1N|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|1O|8|@$9|1P|A|1Q|B|C]]|D|@]|E|$]]|$1|U|3|V|5|6|7|1R|8|@$9|1S|A|1T|B|C]|$9|1U|A|1V|B|C]|$9|1W|A|1X|B|C]|$9|1Y|A|1Z|B|C]|$9|20|A|21|B|C]|$9|22|A|23|B|C]]|D|@$9|24|A|25|1|26]]|E|$]]|$1|W|3|X|5|6|7|27|8|@]|D|@]|E|$]]|$1|Y|3|Z|5|J|7|28|8|@]|D|@]|E|$K|L]]|$1|10|3|11|5|6|7|29|8|@]|D|@]|E|$]]|$1|12|3|-4|5|6|7|2A|8|@]|D|@]|E|$]]]|13|$14|$5|15|16|17|E|$18|19]]]]

Here's an answer that does not use <code>DAYOFWEEK</code> or <code>DAYOFMONTH</code>. It uses <code>DATEADD</code> and <code>DATEPART</code> only.

We'll need two helper functions:

<pre><code>CREATE FUNCTION dbo.day_of_week(@date Date)
RETURNS INT
AS BEGIN
 -- (1 for Sunday, 2 for Monday, etc)
 RETURN DATEPART(dw, DATEADD(year, year(@date)-1900, DATEADD(month, month(@date)-1, DATEADD(day, day(@date)-1, 0))))
END
GO

CREATE FUNCTION dbo.date_from_parts(@year INT, @month INT, @day INT)
RETURNS DATE
AS BEGIN
 RETURN DATEADD(year, @year-1900, DATEADD(month, @month-1, DATEADD(day, @day-1, 0)))
END
GO
</code></pre>

Then using the following example data:

<pre><code>DECLARE @day_of_week INT
SET @day_of_week = 2 -- Monday

DECLARE @year INT
DECLARE @month INT
SET @year = 2016
SET @month = 11
</code></pre>

Let's first obtain the FIRST Monday of the month:

We will add an offset, <code>(day_of_week - day of week of first day of the month) % 7</code>, to the first day of the month.

(Also notice we need the construction <code>((x % n) + n) % n</code> instead of just <code>x % 7</code>, to keep the answer within <code>0</code> and <code>6</code>. For example, just <code>SELECT -3 % 7</code> returns <code>-3</code>! See <a href="https://stackoverflow.com/questions/10472783">Mod negative numbers in SQL just like excel</a>)

Now here's the final construction to obtain the first Monday of the month:

<pre><code>SELECT
 DATEADD(
 dd,
 (((@day_of_week -
 dbo.day_of_week(dbo.date_from_parts(@year, @month, 1))) % 7) + 7) % 7,
 dbo.date_from_parts(@year, @month, 1)
 )
</code></pre>

To obtain the third Monday of the month, add 14 to the second term of this answer.

blocks|key|5063153|text|如果@firstday是每月的第一天|type|unstyled|depth|inlineStyleRanges|offset|length|style|CODE|entityRanges|data|5063154|select+date_add(@firstday,
+++++++++interval+(if(weekday(@firstday)>0,21-weekday(@firstday),14))+day);|code-block|syntax|javascript|5063155|收益率为本月的第三个星期一。|5063156|在2018年的所有月份进行了测试。|5063157|似乎比以前的解决方案更简单。|5063158|关键是在工作日使用if函数。|5063159|如果weekday+=0(第一天是星期一)，如果weekday+>+0，则添加14天，添加21天并减去工作日|5063160|entityMap^0|2|9|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|W|8|@$9|X|A|Y|B|C]]|D|@]|E|$]]|$1|F|3|G|5|H|7|Z|8|@]|D|@]|E|$I|J]]|$1|K|3|L|5|6|7|10|8|@]|D|@]|E|$]]|$1|M|3|N|5|6|7|11|8|@]|D|@]|E|$]]|$1|O|3|P|5|6|7|12|8|@]|D|@]|E|$]]|$1|Q|3|R|5|6|7|13|8|@]|D|@]|E|$]]|$1|S|3|T|5|6|7|14|8|@]|D|@]|E|$]]|$1|U|3|-4|5|6|7|15|8|@]|D|@]|E|$]]]|V|$]]

If <code>@firstday</code> is the first day of the month

<pre><code>select date_add(@firstday,
 interval (if(weekday(@firstday)&gt;0,21-weekday(@firstday),14)) day);
</code></pre>

yields the 3rd monday of the month.

Tested with all months of 2018.

Seems simpler than previous solutions.

The key is the if function on weekday.

if weekday = 0 (first day is a monday), add 14 days
if weekday > 0, add 21 days and subtract the weekday

blocks|key|2949384|text|因此，更一般的情况下，如果您可以使用以下命令获得日期的"week+of+month“：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2949385|(FLOOR((DAYOFMONTH(given)+-+1)+/+7))+AS+'week_of_month‘|2949386|我认为它为给定日期提供了精确的以0为基数的周索引。然后，您可以使用该值来查找任意n个，如下所示：|2949387|其中(week_of_month)+=n，工作日=+{+weekday+}|2949388|您也可以通过以下方式使用上面的代码来获取"last+{weekday}“：|2949389|其中(week_of_month+>=+4)+and+weekday+=+{weekday}|2949390|请注意，week_of_month的范围可以是0到5(例如，第一天在星期六的31天的月份将有第6周的第31天(5作为基于0的索引))|2949391|希望这能帮到你。|2949392|好的再来一点……您可以将上述内容定义为如下所示的函数：|2949393|CREATE+FUNCTION+WEEKOFMONTH(given+date)+RETURNS+int+DETERMINISTIC+RETURN+(FLOOR((DAYOFMONTH(given)+-+1)+/+7))|offset|length|style|CODE|2949394|并添加另一个函数：|2949395|CREATE+FUNCTION+WEEKNAME(given+date)+RETURNS+text+CHARACTER+SET+utf8+COLLATE+utf8_unicode_ci+DETERMINISTIC+RETURN+(CONCAT(ELT(WEEKOFMONTH(given)%2B1,'1st+','2nd+','3rd+','4th/Last+','5th/Last+'),DAYNAME(given)))|2949396|然后你可以简单地说像这样|2949397|SELECT+*+FROM+dataTable+WHERE+WEEKNAME(your_date_field)+=+"3rd+Wednesday"|code-block|syntax|javascript|2949398|..。我纠结于如何从工作日返回4/5，并决定在理论上为两者添加"/Last“，即这是”足够好“的，如果一个人想测试4，5或最后。使用此命令，您可以执行以下操作：|2949399|SELECT+*+FROM+dataTable+WHERE+WEEKNAME(your_date_field)+LIKE+"%25Last%25"|2949400|entityMap^0|0|0|0|0|0|0|0|0|0|0|31|0|0|0|5T|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|1E|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|1F|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|1G|8|@]|9|@]|A|$]]|$1|F|3|G|5|6|7|1H|8|@]|9|@]|A|$]]|$1|H|3|I|5|6|7|1I|8|@]|9|@]|A|$]]|$1|J|3|K|5|6|7|1J|8|@]|9|@]|A|$]]|$1|L|3|M|5|6|7|1K|8|@]|9|@]|A|$]]|$1|N|3|O|5|6|7|1L|8|@]|9|@]|A|$]]|$1|P|3|Q|5|6|7|1M|8|@]|9|@]|A|$]]|$1|R|3|S|5|6|7|1N|8|@$T|1O|U|1P|V|W]]|9|@]|A|$]]|$1|X|3|Y|5|6|7|1Q|8|@]|9|@]|A|$]]|$1|Z|3|10|5|6|7|1R|8|@$T|1S|U|1T|V|W]]|9|@]|A|$]]|$1|11|3|12|5|6|7|1U|8|@]|9|@]|A|$]]|$1|13|3|14|5|15|7|1V|8|@]|9|@]|A|$16|17]]|$1|18|3|19|5|6|7|1W|8|@]|9|@]|A|$]]|$1|1A|3|1B|5|15|7|1X|8|@]|9|@]|A|$16|17]]|$1|1C|3|-4|5|6|7|1Y|8|@]|9|@]|A|$]]]|1D|$]]

So perhaps MORE GENERALLY if you can get the "week of month" for the date using this:

(FLOOR((DAYOFMONTH(given) - 1) / 7)) AS 'week_of_month'

which I believe provides an accurate 0 based week-of-month index for a given date. then you can use the value to find any nth as in:

WHERE (week_of_month) = n AND weekday = {weekday}

you can also use the above to get the "last {weekday}" by:

WHERE (week_of_month >= 4) and weekday = {weekday} 

note that the week_of_month can range from 0 to 5 (e.g., a 31 day month whose 1st falls on Saturday will have the 31st in the 6th week (5 as a 0 based index)

hope this helps ... 

OK a bit more ... you might define the above as a function as in:

<code>CREATE FUNCTION WEEKOFMONTH(given date) RETURNS int DETERMINISTIC RETURN (FLOOR((DAYOFMONTH(given) - 1) / 7))</code>

and add another function:

<code>CREATE FUNCTION WEEKNAME(given date) RETURNS text CHARACTER SET utf8 COLLATE utf8_unicode_ci DETERMINISTIC RETURN (CONCAT(ELT(WEEKOFMONTH(given)+1,'1st ','2nd ','3rd ','4th/Last ','5th/Last '),DAYNAME(given)))</code>

then you can simply say things like

<pre><code>SELECT * FROM dataTable WHERE WEEKNAME(your_date_field) = "3rd Wednesday"
</code></pre>

... I struggled with how the 4th/5th should be returned from WEEKDAY and settled on adding "/Last" for both under the theory that this is "good enough" should one want to test for either 4th, 5th or Last. Using this you can do:

<pre><code>SELECT * FROM dataTable WHERE WEEKNAME(your_date_field) LIKE "%Last%"
</code></pre>

blocks|key|2096361|text|DECLARE+@YEAR+DATE='2019-01-01'
SELECT+DATEADD(+d,+23-(DATEPART(dw,@YEAR+)%2521),@YEAR+)|type|code-block|depth|inlineStyleRanges|entityRanges|data|syntax|javascript|2096362|这将有助于获得你想要的月份的第三个星期一。|unstyled|2096363|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$B|C]]|$1|D|3|E|5|F|7|J|8|@]|9|@]|A|$]]|$1|G|3|-4|5|F|7|K|8|@]|9|@]|A|$]]]|H|$]]

<pre><code>DECLARE @YEAR DATE='2019-01-01'
SELECT DATEADD( d, 23-(DATEPART(dw,@YEAR )%21),@YEAR )
</code></pre>

This will help to get the third Monday of whatever month you want.

i need to trigger a notification. this notification has to be triggered every third monday of every month.

How to calculate a third monday of every month using SQL statement?

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我需要触发一个通知。此通知必须在每个月的第三个星期一触发。

问如何使用SQL语句计算每月的第三个星期一？
EN

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问如何使用SQL语句计算每月的第三个星期一？EN