SQL Datepart - 从星期一而不是星期日开始一周
我在使用 datepart 的查询中遇到了一个问题。我们一直在使用以下查询返回过去三周的数据，但是我们最近发现公司使用周一至周日的报告周，而以下查询默认为周日至周六。我已经尝试过 "SET LANGUAGE BRITISH" 和 "SET DATEFIRST 1" 但我不能很好地掌握这些函数，因为它们不会改变我的查询结果。我应该提到我们正在 sql server 2000 上运行。如果您知道解决方案，我们将不胜感激：
declare @name varchar(50);
set @name = 'A name here';
SELECT week, year, CallCount, GoodCalls, CAST(CAST(GoodCalls as float)/CAST(CallCount as float)as decimal (18,4)) as NCP_perc
FROM
(SELECT TOP 3 datepart(ww, a.date_c) as week
,datepart(year, a.date_c) as year
,SUM(CallCount_wo_Xfer) as CallCount
,ROUND(SUM(CAST((CallCount_wo_Xfer*NCP_wo_Xfer)as float)),0) as GoodCalls
FROM db1 A
inner join db2 B
on a.Agent = b.Name collate database_default
inner join db3 C
on b.id = c.id collate database_default
where c.manager = @name
group by datepart(year, a.date_c), datepart(ww, a.date_c)) AS T
order by year desc, week desc
I have run across an issue in a query using datepart. We have been using the following query to return the last three weeks of data, however we recently found out that corporate is using a reporting week from Monday-Sunday, while the below query is defaulted to Sunday-Saturday. I have tried "SET LANGUAGE BRITISH" and "SET DATEFIRST 1" but I must not have a good grasp on these functions as they do not change my query results. I should mention that we are running on sql server 2000. If you know a solution your help would be appreciated:
declare @name varchar(50);
set @name = 'A name here';
SELECT week, year, CallCount, GoodCalls, CAST(CAST(GoodCalls as float)/CAST(CallCount as float)as decimal (18,4)) as NCP_perc
FROM
(SELECT TOP 3 datepart(ww, a.date_c) as week
,datepart(year, a.date_c) as year
,SUM(CallCount_wo_Xfer) as CallCount
,ROUND(SUM(CAST((CallCount_wo_Xfer*NCP_wo_Xfer)as float)),0) as GoodCalls
FROM db1 A
inner join db2 B
on a.Agent = b.Name collate database_default
inner join db3 C
on b.id = c.id collate database_default
where c.manager = @name
group by datepart(year, a.date_c), datepart(ww, a.date_c)) AS T
order by year desc, week desc
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
星期一是第二天，所以尝试一下
Monday is day 2 so try
如果您想从星期一开始，设置 DateFirst 2 将跳过前两天
使用
设置日期第一 1
Set DateFirst 2 will skip the first two days, if you want to start from Monday
Use
Set DateFirst 1