SQL Server 返回意外的周数 [英] SQL Server returns unexpected week number

问题描述

我在一张桌子上有一些订单，2011 年的最后订单日期是 12 月 20 日.

I have some orders in a table and the last order date of 2011 is 20th Dec.

我正在使用 sql 命令来计算给定周内的订单数:

I'm using a sql command to calculate the number of orders in a given week:

SELECT CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) AS week, 
  COUNT(1) as orders 
FROM order_table 
where DATENAME(YEAR,convert(datetime,order_date,103)) = '2011' 
GROUP BY CONVERT(VARCHAR(3),DATENAME(week,convert(datetime,order_date,103))) 
order by week asc

它返回给我以下一些结果:

It returns me the some of the following results:

Week | Orders  
41   | 42  
42   | 110  
43   | 115  
...
...  
51   | 155  
52   | 15

问题是我提到的 2011 年最后一个订单日期是 2011 年 12 月 20 日，不能是第 52 周，所以必须是第 51 周.

The trouble with this is is that the last order date of 2011 as mentioned that I have is 20th Dec 2011, which can't be week 52 so must be week 51.

我有一些其他统计数据(来自另一个系统，而不是 SQL 服务器)，它给了我一些其他数字，上周是 51，我毫不怀疑这是正确的.因此，如果人们同时查看两者，将会有疑问！

I've got some other stats(off another system, not SQL server) which giving me some other figures and the last week on it is 51 which I have no doubt is correct. So there's going to be a queries if people are looking both!

有人有任何想法或知道如何排序吗?

Anyone have any idea or know how to sort this?

谢谢，

推荐答案

2011 年 12 月 20 日的 iso_week 是 51.所以也许这就是您所需要的.

The iso_week of 20th Dec 2011 is 51. So maybe that is what you need.

SELECT datepart(iso_week, '2011-12-20')

这篇关于SQL Server 返回意外的周数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！