在 SQL Server 中使用 DATEPART 时出现错误的周数

发布于 2024-08-20 19:29:47 字数 349 浏览 8 评论 0原文

我遇到了在 SQL Server 2000 中返回第 7 周结果的问题。

select datepart(ww, '20100208')

但是 08.02.2010 应该是 >第 6 周根据 ISO 8601 规范！这导致交货周计算出现问题。

我应该怎样做才能根据 ISO 8601 获取周数值？

原文

I've got the problem that

select datepart(ww, '20100208')

is returning as result week 7 in SQL Server 2000. But 08.02.2010 should be week 6 according to the ISO 8601 specification! This is causing problems in delivery week calculations.

What should I do to get week number values according to ISO 8601?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

双手揣兜 2024-08-27 19:29:48

您可以在 SQL 2008 中非常轻松地执行此操作，因为它现在支持 isoww 作为第一个日期部分参数。然而，这在 SQL 2000（或 2005）中并不存在。这篇文章中有一个函数它会在 SQL 2000/2005 中为您完成此操作。

万一博客离线了，这里有这个功能。前往帖子了解有关 ISO 和非 ISO 周的更多信息。

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

You can do this within SQL 2008 very easily as it now supports isoww as the first datepart argument. However, this wasn't in SQL 2000 (or 2005). There is a function in this article which will do it for you in SQL 2000/2005.

In case the blog goes offline, here is the function. Go to the post to learn more about ISO and non-ISO weeks.

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

回复收藏 0 原文