问题描述
我有基于日期和计算列的日历表(下面的代码)。问题是由于逻辑52 * 7 = 364,它使我回到了第53周,因此实际上,我们每年总是会得到一些备用天,例如2014年的这些天:
I have this Calendar table (code below) based on the Date and then Calculated columns. The problem is that it returns me 53rd weeks because of the logic 52 * 7 = 364 so actually we always get some "spare" days per year, as happens for example in 2014 for these days:
2014-12-28
2014-12-29
2014-12-30
2014-12-31
2014-12-28
2014-12-29
2014-12-30
2014-12-31
在我工作的公司中,我们必须使用ISO日历周。如何更改此表以获取ISO日历周而不是此周?
In the company I work, we must use ISO calendar weeks. How do I change this table in order to get the ISO calendar weeks instead of this?
我忘了提及我使用的方法:SQL Server 2008 R2和2012 。
CREATE TABLE [dbo].[TO_BDB_NOSSCE_CALENDAR](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DATE] [date] NOT NULL,
[YEAR] AS (datepart(year,[DATE])) PERSISTED,
[SEMESTER] AS (case when datepart(month,[DATE])<(7) then '1' else '2' end) PERSISTED NOT NULL,
[TRIMESTER] AS (case when datepart(month,[DATE])<(4) then '1' else case when datepart(month,[DATE])<(7) then '2' else case when datepart(month,[DATE])<(10) then '3' else '4' end end end) PERSISTED NOT NULL,
[MONTH] AS (case when len(CONVERT([varchar](2),datepart(month,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(month,[DATE]),0) else CONVERT([varchar](2),datepart(month,[DATE]),0) end) PERSISTED,
[WEEK] AS (case when len(CONVERT([varchar](2),datepart(week,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(week,[DATE]),0) else CONVERT([varchar](2),datepart(week,[DATE]),0) end),
[DAY] AS (case when len(CONVERT([varchar](2),datepart(day,[DATE]),0))=(1) then '0'+CONVERT([varchar](2),datepart(day,[DATE]),0) else CONVERT([varchar](2),datepart(day,[DATE]),0) end) PERSISTED,
[WEEKNUMBER] AS (datepart(week,[DATE])),
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
推荐答案
在SQL Server 2008+中,可以使用以下内容:
In SQL Server 2008+, you can use the following:
SELECT DATEPART(ISO_WEEK, <DATE>)
以下SO问题讨论了导出ISO周的其他方法:
The following SO question discusses other methods to derive the ISO week:
这篇关于T-SQL计算列如何获取ISO周数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!