如何在T-SQL查询中编程建立ISO周数表？
如何在T-SQL查询中编程建立ISO周数表？
提问于 2013-01-03 14:01:03
有谁知道如何使用T-SQL查询来建立一周的临时表？
我听说有很多类型的计算，格里高利或其他。我的需求是ISO Week no，绑定到临时表取决于week No。
临时表有两列：ISOWeekNo和WeekName
ISOWeekNo WeekName
1 01 Jan 2013 To 07 Jan 2013
2 08 Jan 2013 To 14 Jan 2013如何在基于ISO Week No的T-SQL Query中以编程方式构建？
更新:我只想传递参数year。例如: 2013年
回答 3
Stack Overflow用户
回答已采纳
发布于 2013-01-03 15:50:02
编辑:添加WHERE子句，以仅终止查找的年份。
这似乎符合维基百科的描述，我相信还有优化的空间。Mikael，我为friendly专栏复制了你的格式代码，谢谢。由于使用了ISOWEEK日期部分，此代码将在SQL Server 2008及更高版本上运行。
use tempdb
go
DECLARE @Year SMALLINT = 2013
,@FirstISOWKDay DATETIME
;WITH FindISOWEEKFirstDay AS
(
SELECT DT = DATEADD(DAY, -7, DATEFROMPARTS(@Year, 1, 1))
UNION ALL
SELECT DATEADD(DAY, 1, DT)
FROM FindISOWEEKFirstDay
WHERE DATEADD(DAY, 1, DT) < DATEADD(DAY, 14, DATEFROMPARTS(@Year, 1, 1))
)
SELECT TOP 1 @FirstISOWKDay = DT
FROM FindISOWEEKFirstDay
WHERE DATEPART(ISO_WEEK, DT) = 1
ORDER BY DT ASC -- Eliminate probability of arb sorting (Thanks Mikael)
;WITH Base10 (n) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1
)
,Base1000 (n) AS
(
SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1
FROM Base10 T1, Base10 T2, Base10 T3
)
SELECT Start = DATEADD(DAY, n*7, @FirstISOWKDay)
,[End] = DATEADD(DAY, n*7 + 6, @FirstISOWKDay)
,Friendly = CONVERT(VARCHAR(101), DATEADD(DAY, n*7, @FirstISOWKDay), 106)+' To '+CONVERT(VARCHAR(101), DATEADD(DAY, n*7 + 6, @FirstISOWKDay), 106)
,ISOWEEK = DATEPART(ISO_WEEK, DATEADD(DAY, n*7, @FirstISOWKDay))
FROM Base1000
-- Filter to terminate, resulting only in sought year's calendar
WHERE DATEPART(YEAR, DATEADD(DAY, n*7 + 6, @FirstISOWKDay)) = @YearStack Overflow用户
发布于 2013-01-03 14:33:35
declare @Year int;
set @Year = 2016;
with C as
(
select datefromparts(@Year, 1, 1) as D
union all
select dateadd(day, 1, C.D)
from C
where C.D < datefromparts(@Year, 12, 31)
)
select datepart(iso_week, C.D) as ISOWeekNo,
convert(varchar(101), min(C.D), 106)+' To '+convert(varchar(101), max(C.D), 106) as WeekName
from C
group by datepart(iso_week, C.D),
case when datepart(month, C.D) = 12 and
datepart(iso_week, C.D) > 50
then 1
else 0
end
order by min(C.D)
option (maxrecursion 0);结果：
ISOWeekNo WeekName
----------- --------------------------
53 01 Jan 2016 To 03 Jan 2016
1 04 Jan 2016 To 10 Jan 2016
2 11 Jan 2016 To 17 Jan 2016
3 18 Jan 2016 To 24 Jan 2016
4 25 Jan 2016 To 31 Jan 2016
5 01 Feb 2016 To 07 Feb 2016
6 08 Feb 2016 To 14 Feb 2016
7 15 Feb 2016 To 21 Feb 2016
.
.
.
47 21 Nov 2016 To 27 Nov 2016
48 28 Nov 2016 To 04 Dec 2016
49 05 Dec 2016 To 11 Dec 2016
50 12 Dec 2016 To 18 Dec 2016
51 19 Dec 2016 To 25 Dec 2016
52 26 Dec 2016 To 31 Dec 2016Stack Overflow用户
发布于 2013-01-04 06:09:50
这可能会有所帮助：
Select date '2012-12-31' + level*7 WK_STARTS_DT
, to_char(date '2012-12-31' + level*7, 'IW') ISO_WEEK
, to_char(date '2012-12-31' + level*7, 'WW') WEEK
From dual
Connect By Level <=
(
Select Round( (ADD_MONTHS(TRUNC(SYSDATE,'Y'),12)-TRUNC(SYSDATE,'Y') )/7, 0) From dual
) --365/7
/
WK_STARTS_DT ISO_WEEK WEEK
------------------------------------
1/7/2013 02 01
1/14/2013 03 02
1/21/2013 04 03
......
2/4/2013 06 05
2/11/2013 07 06
......
3/4/2013 10 09要确认周数，请访问http://www.epochconverter.com/date-and-time/weeknumbers-by-year.php?year=2013
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/14133816
复制相关文章
相似问题
腾讯云开发者
