SQL获取“ ISO年” ISO周 [英] SQL Get "ISO Year" for ISO Week

问题描述

我需要计算一周的分配年份。例如，将2003年12月29日指定为2004年的第一周（我认为这仅适用于欧洲）。您可以使用以下代码查看此内容：

I need to calculate the year a week is assigned to. For example the 29th december of 2003 was assigned to week one of year 2004 (this is only for europe, I think). You can take a look at this with this code:

SELECT DATEPART(isowk, '20141229');

但是现在我需要一种简单的方法来获取本周的年份。我目前做的不是那么优雅：

But now I need an easy way to get the year this week is assigned to. What I currently do is not that elegant:

DECLARE @week int, @year int, @date char(8)

--set @date = '20150101'
set @date = '20141229'


SET @week = cast(datepart(isowk, @date) as int)

if @week = 1
begin
      if DATEPART(MONTH, @date) = 12
      begin
            set @year = DATEPART(year, @date) + 1
      end
      else
      begin
            set @year = DATEPART(year, @date)
      end
end

select @date "DATE", @week "WEEK", @year "YEAR"

如果有人知道更优雅的方法，那将是很好的：-）

If anybody knew a more elegant way, that would be nice :-)

推荐答案

此解决方案未返回正确的结果日期的值'1-1-2027'。

This solution does not return the correct value for the date '1-1-2027'.

以下内容将返回所有日期的正确值我测试了（并且测试了很多）。

The following will return the correct value with all dates i tested (and i tested quite a few).

SELECT YEAR(DATEADD(day, 26 - DATEPART(isoww, '2012-01-01'), '2012-01-01'))

摘自： https://capens.net/content/sql-year-iso-week

这篇关于SQL获取“ ISO年” ISO周的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！