在 MS SQL Server 2005 中获取日期的周数? [英] Getting week number off a date in MS SQL Server 2005?

问题描述

是否可以创建一个选择周数(不是星期几 - 或一周中的天数)的 sql 语句.我正在创建一个视图来选择此额外信息以及其他几个字段，因此无法使用存储过程.我知道可以创建一个 UDF 来完成这个技巧，但如果可能的话，我宁愿只向这个数据库添加一个视图，而不是一个视图和一个函数.

Is it possible to create an sql statement that selects the week number (NOT the day of week - or the day number in a week). I'm creating a view to select this extra information along with a couple of other fields and thus can not use a stored procedure. I'm aware that it's possible to create a UDF to do the trick, but if at all possible i'd rather only have to add a view to this database, than both a view and a function.

有什么想法吗?同样，我来自哪里，一周从星期一开始，第 1 周是一年中的第一周，至少有 4 天.

Any ideas? Also where i come from, the week starts monday and week 1 is the first week of the year with atleast 4 days.

如何计算给定的周数日期?

推荐答案

请注意，根据文化，正确的周数存在差异.周数取决于几个不同国家/地区的假设，请参阅 维基百科关于此事的文章.有一个适用于周数的 ISO 标准 (ISO 8601).

Be aware that there are differences in what is regarded the correct week number, depending on the culture. Week numbers depend on a couple of assumptions that differ from country to country, see Wikipedia article on the matter. There is an ISO standard (ISO 8601) that applies to week numbers.

SQL 服务器集成的 DATEPART() 函数不一定能做正确的事.SQL Server 假设第 1 周的第 1 天是 1 月 1 日，对于许多应用程序来说这是错误的.

The SQL server integrated DATEPART() function does not necessarily do The Right Thing. SQL Server assumes day 1 of week 1 would be January 1, for many applications that's wrong.

正确计算周数并非易事，并且可以在网络上找到不同的实现.例如，有一个 UDF 可以计算 1930-2030 年的 ISO 周数，成为众多其他人中的一员.你必须检查什么对你有用.

Calculating week numbers correctly is non-trivial, and different implementations can be found on the web. For example, there's an UDF that calculates the ISO week numbers from 1930-2030, being one among many others. You'll have to check what works for you.

这本书来自 Books Online(尽管您可能想使用来自乔纳斯林肯的回答，BOL 版本似乎不正确):

This one is from Books Online (though you probably want to use the one from Jonas Lincoln's answer, the BOL version seems to be incorrect):

CREATE FUNCTION ISOweek  (@DATE DATETIME)
RETURNS INT
AS
BEGIN
   DECLARE @ISOweek INT
   SET @ISOweek = DATEPART(wk,@DATE) 
                  +1 
                  -DATEPART(wk,CAST(DATEPART(yy,@DATE) AS CHAR(4))+'0104')
   -- Special cases: Jan 1-3 may belong to the previous year
   IF (@ISOweek=0)
      SET @ISOweek = dbo.ISOweek(CAST(DATEPART(yy,@DATE) - 1
                     AS CHAR(4))+'12'+ CAST(24+DATEPART(DAY,@DATE) AS CHAR(2)))+1
   -- Special case: Dec 29-31 may belong to the next year
   IF ((DATEPART(mm,@DATE)=12) AND
      ((DATEPART(dd,@DATE)-DATEPART(dw,@DATE))>= 28))
      SET @ISOweek=1
   RETURN(@ISOweek)
END
GO

这篇关于在 MS SQL Server 2005 中获取日期的周数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！