文章/答案/技术大牛

发布

社区首页 >问答首页 >在SQL中计算给定月份的周日

问在SQL中计算给定月份的周日
EN

Stack Overflow用户

提问于 2012-10-18 22:19:05

回答 2查看 190关注 0票数 0

我需要找出给定月份的第一个/第二个/第三个/第四个/最后一个工作日。

我还需要周末来安排循环。

我可以找到第一个工作日：

SELECT CASE Dayofweek('2012-11-04') 
         WHEN 1 THEN Date_add('2012-11-04', INTERVAL 1 DAY) 
         WHEN 7 THEN Date_add('2012-11-04', INTERVAL 2 DAY) 
         ELSE '2012-11-04' 
       END AS dateStart

类似地，我需要得到第二个，第三个，等等。

sql

date-arithmetic

回答 2

Stack Overflow用户

发布于 2012-10-18 22:26:36

在SQL Server上，DATEPART(dw, your_date)会告诉您这是否是周末。通常，1表示周六，7表示周日，但根据服务器配置的不同，可能会发生变化。请阅读datepart函数以了解其工作原理

票数 0

Stack Overflow用户

发布于 2012-10-18 22:50:18

在MySQL中：

CREATE TABLE calendar
(
  date date not null,
  isBus bit
);
ALTER TABLE calendar
ADD CONSTRAINT PK_calendar
PRIMARY KEY(date);


INSERT INTO calendar VALUES ('2012-10-01', 1);
INSERT INTO calendar VALUES ('2012-10-02', 1);
INSERT INTO calendar VALUES ('2012-10-03', 1);
INSERT INTO calendar VALUES ('2012-10-04', 1);
INSERT INTO calendar VALUES ('2012-10-05', 1);
INSERT INTO calendar VALUES ('2012-10-06', 0);
INSERT INTO calendar VALUES ('2012-10-07', 0);
INSERT INTO calendar VALUES ('2012-10-08', 1);
INSERT INTO calendar VALUES ('2012-10-09', 1);
INSERT INTO calendar VALUES ('2012-10-10', 1);
INSERT INTO calendar VALUES ('2012-10-11', 1);
INSERT INTO calendar VALUES ('2012-10-12', 1);
INSERT INTO calendar VALUES ('2012-10-13', 0);
INSERT INTO calendar VALUES ('2012-10-14', 0);
INSERT INTO calendar VALUES ('2012-10-15', 1);
INSERT INTO calendar VALUES ('2012-10-16', 1);
INSERT INTO calendar VALUES ('2012-10-17', 1);
INSERT INTO calendar VALUES ('2012-10-18', 1);
INSERT INTO calendar VALUES ('2012-10-19', 1);
INSERT INTO calendar VALUES ('2012-10-20', 0);
INSERT INTO calendar VALUES ('2012-10-21', 0);
INSERT INTO calendar VALUES ('2012-10-22', 1);
INSERT INTO calendar VALUES ('2012-10-23', 1);
INSERT INTO calendar VALUES ('2012-10-24', 1);
INSERT INTO calendar VALUES ('2012-10-25', 1);
INSERT INTO calendar VALUES ('2012-10-26', 1);
INSERT INTO calendar VALUES ('2012-10-27', 0);
INSERT INTO calendar VALUES ('2012-10-28', 0);
INSERT INTO calendar VALUES ('2012-10-29', 1);
INSERT INTO calendar VALUES ('2012-10-30', 1);
INSERT INTO calendar VALUES ('2012-10-31', 1);
INSERT INTO calendar VALUES ('2012-11-01', 1);
INSERT INTO calendar VALUES ('2012-11-02', 1);
INSERT INTO calendar VALUES ('2012-11-03', 0);
INSERT INTO calendar VALUES ('2012-11-04', 0);
INSERT INTO calendar VALUES ('2012-11-05', 1);
INSERT INTO calendar VALUES ('2012-11-06', 1);
INSERT INTO calendar VALUES ('2012-11-07', 1);
INSERT INTO calendar VALUES ('2012-11-08', 1);
INSERT INTO calendar VALUES ('2012-11-09', 1);
INSERT INTO calendar VALUES ('2012-11-10', 0);
INSERT INTO calendar VALUES ('2012-11-11', 0);
INSERT INTO calendar VALUES ('2012-11-12', 1);
INSERT INTO calendar VALUES ('2012-11-13', 1);
INSERT INTO calendar VALUES ('2012-11-14', 1);
INSERT INTO calendar VALUES ('2012-11-15', 1);
INSERT INTO calendar VALUES ('2012-11-16', 1);
INSERT INTO calendar VALUES ('2012-11-17', 0);
INSERT INTO calendar VALUES ('2012-11-18', 0);
INSERT INTO calendar VALUES ('2012-11-19', 1);
INSERT INTO calendar VALUES ('2012-11-20', 1);
INSERT INTO calendar VALUES ('2012-11-21', 1);
INSERT INTO calendar VALUES ('2012-11-22', 1);
INSERT INTO calendar VALUES ('2012-11-23', 1);
INSERT INTO calendar VALUES ('2012-11-24', 0);
INSERT INTO calendar VALUES ('2012-11-25', 0);
INSERT INTO calendar VALUES ('2012-11-26', 1);
INSERT INTO calendar VALUES ('2012-11-27', 1);
INSERT INTO calendar VALUES ('2012-11-28', 1);
INSERT INTO calendar VALUES ('2012-11-29', 1);
INSERT INTO calendar VALUES ('2012-11-30', 1);

然后在10月份的所有星期四(5)：

select @rowid:=@rowid+1, calendar.* from calendar, (SELECT @rowid:=0) as init 
where dayofweek(date) = 5
and month(date) = 10
order by date;

10月的第二个星期四：

select * from
(select @rowid:=@rowid+1 as seqNo, calendar.* from calendar, (SELECT @rowid:=0) as init 
where dayofweek(date) = 5
and month(date) = 10
order by date) x
where seqNo = 2;

票数 0

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/12956815

复制

相似问题

问在SQL中计算给定月份的周日
EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中计算给定月份的周日EN

回答 2

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问在SQL中计算给定月份的周日
EN