T-SQL calculated column how to get ISO week number

Question

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

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?

I forgot to mention that I use: SQL Server 2008 R2 and 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

Keep in mind that in addition to the week number, you probably also want the week year (which is not necessarily equal to the date year). January 1st 2010 was part of the last ISO week of 2009. You really don't want to present that as "2010 week 53", that would be totally wrong. Also keep in mind that, as indicated in that specific example, ISO week numbers can *still* go up to 53. — , Jan 15 '15 at 17:34

score 6 · Answer 1 · edited May 23 '17 at 12:25

6

In SQL Server 2008+, you can use the following:

SELECT DATEPART(ISO_WEEK, <DATE>)

The following SO question discusses other methods to derive the ISO week:

Isoweek in SQL Server 2005

edited May 23 '17 at 12:25

Community

1
1

answered Jan 15 '15 at 17:30

SQLDiver

1,948
2
11
14

1

Perfect, thanks! I forgot to mention in the original post that I have versions 2008 R2 and 2012... So there is always a more simple way! – KathyBlue Jan 16 '15 at 08:51
3

Note that along with the week number it is important to include the ISO week **year**, because it can differ from the normal date year. It can be calculated like this: `YEAR(DATEADD(wk, DATEDIFF(d, 0, @dt) / 7, 3))` – Michel de Ruiter Apr 20 '16 at 10:16

T-SQL calculated column how to get ISO week number

1 Answers1

Linked