错误的周数使用DATEPART在SQL Server中 [英] Wrong week number using DATEPART in SQL Server
问题描述
我有这个问题,
select datepart(ww, '20100208')
作为结果 第7周 返回。 > 08.02.2010 应该是 第6周 根据 ISO 8601 规格!
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.
如何根据ISO 8601获取星期数值?
推荐答案
您可以非常容易地在SQL 2008中执行此操作,因为它现在支持 isoww 作为第一个datepart参数。但是,这不是SQL 2000(或2005)。 此中的功能文章将在SQL 2000/2005中为您做。
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
这篇关于错误的周数使用DATEPART在SQL Server中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!