A Simple Formula to Calculate the ISO Week Number

Question

Post reply

A Simple Formula to Calculate the ISO Week Number

Viewing 15 posts - 46 through 60 (of 77 total)

You must be logged in to reply to this topic. Login to reply

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

robert.sterbal 56890 (10/23/2015)

...

I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendar

Jeff - there's someone older than you on ssc 😉

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Robert Sterbal SSChampion Points: 11028 More actions · Answer 2

ChrisM@Work (10/23/2015)

robert.sterbal 56890 (10/23/2015)

...

I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendar

Jeff - there's someone older than you on ssc 😉

I may or may not be older, but Jeff is definitely wiser and smarter!

412-977-3526 call/text

Beugen SSC Journeyman Points: 94 More actions · Answer 3

Hi

Thanks for this simple and effective code.

Is there a similar easy way to get the year of that week we are looking for? Or the "ISO-year"?

Lets say I put in the date '2014-12-31' and get the ISO-WeekNumber = 1, I would also like to get the what year that weeknumber is for (in this case 2015).

I guess I can try to find this out, but if you guys already have some really efficient code to do this, it would be appreciated.

br

Gert

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

robert.sterbal 56890 (10/23/2015)

I like the way your formula skips the leap year problem.

I still remember when the church moved the calendar by a couple of weeks - https://en.wikipedia.org/wiki/Gregorian_calendar

That would be the reason why I don't use SQL Server for dates before 1753. 😉 Heh... Y2K was easy after that one. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

marc.corbeel Default port Points: 1484 More actions · Answer 5

marc.corbeel

Default port

Points: 1484

October 23, 2015 at 9:04 am

#1835216

I don't believe it's a correct calculation for all situations. It doesn't look at the first day of a week. In Europe that is Monday, in other parts of the world this is Sunday.

For instance Sunday 3 January 2016 for us in Europe is week 53 (of year 2015). In US this week 1.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 6

marc.corbeel (10/23/2015)

I don't believe it's a correct calculation for all situations. It doesn't look at the first day of a week. In Europe that is Monday, in other parts of the world this is Sunday.

For instance Sunday 3 January 2016 for us in Europe is week 53 (of year 2015). In US this week 1.

That's why we have the ISO standards, including an ISO week.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 7

Beugen (10/23/2015)

Hi

Thanks for this simple and effective code.

Is there a similar easy way to get the year of that week we are looking for? Or the "ISO-year"?

Lets say I put in the date '2014-12-31' and get the ISO-WeekNumber = 1, I would also like to get the what year that weeknumber is for (in this case 2015).

I guess I can try to find this out, but if you guys already have some really efficient code to do this, it would be appreciated.

br

Gert

The "Thursday of the week" calculation from the article is your friend here. I just added the DATEPART(yy,) to that below.



 SELECT  [2014-12-28] = DATEPART(yy,DATEDIFF(dd,0,'2014-12-28')/7*7+3)

        ,[2014-12-29] = DATEPART(yy,DATEDIFF(dd,0,'2014-12-29')/7*7+3)

        ,[2014-12-30] = DATEPART(yy,DATEDIFF(dd,0,'2014-12-30')/7*7+3)

        ,[2014-12-31] = DATEPART(yy,DATEDIFF(dd,0,'2014-12-31')/7*7+3)

        ,[2015-01-01] = DATEPART(yy,DATEDIFF(dd,0,'2015-01-01')/7*7+3)

        ,[2015-12-31] = DATEPART(yy,DATEDIFF(dd,0,'2015-12-31')/7*7+3)

        ,[2016-01-01] = DATEPART(yy,DATEDIFF(dd,0,'2016-01-01')/7*7+3)

        ,[2016-01-02] = DATEPART(yy,DATEDIFF(dd,0,'2016-01-02')/7*7+3)

        ,[2016-01-03] = DATEPART(yy,DATEDIFF(dd,0,'2016-01-03')/7*7+3)

        ,[2016-01-04] = DATEPART(yy,DATEDIFF(dd,0,'2016-01-04')/7*7+3)

;

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 8

marc.corbeel (10/23/2015)

I don't believe it's a correct calculation for all situations. It doesn't look at the first day of a week. In Europe that is Monday, in other parts of the world this is Sunday. For instance Sunday 3 January 2016 for us in Europe is week 53 (of year 2015). In US this week 1.

Not in ISO. Again, it's part of the reason for this ISO standard. Doesn't matter where you're at in the world, the ISO standard dictates that a week will always start on Monday. The code in the article isn't meant to solve for anything else. It doesn't rely on anything having to do with @@DateFirst, settings, which is part of the beauty of the formula and the necessity of the ISO standard.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

marc.corbeel Default port Points: 1484 More actions · Answer 9

marc.corbeel

Default port

Points: 1484

October 23, 2015 at 9:26 am

#1835229

removed

marc.corbeel Default port Points: 1484 More actions · Answer 10

marc.corbeel

Default port

Points: 1484

October 23, 2015 at 9:27 am

#1835230

Does this means that Sunday 3 January 2016 is also week 53 in US standards?

Robert Sterbal SSChampion Points: 11028 More actions · Answer 11

Robert Sterbal

SSChampion

Points: 11028

October 23, 2015 at 9:42 am

#1835234

On a related note:

What is the ISO standard for midnight?

2015-03-04 23:59

2015-03-0? 00:00

2015-03-05 00:01

Does ? = 4 or 5

412-977-3526 call/text

marc.corbeel Default port Points: 1484 More actions · Answer 12

marc.corbeel

Default port

Points: 1484

October 23, 2015 at 9:55 am

#1835240

In my opinion, "midnight" doesn't exist in programmers' language.

It equals the beginning of the next day, so my answer is "4"

Correction: I mean "5"

dmbaker SSCertifiable Points: 5068 More actions · Answer 13

dmbaker

SSCertifiable

Points: 5068

October 23, 2015 at 10:52 am

#1835255

Thanks for the article...5 stars (especially since you didn't say "performant" anywhere) 😀

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 14

marc.corbeel (10/23/2015)

Does this means that Sunday 3 January 2016 is also week 53 in US standards?

No. It means that it's week 53 according to ISO standards.

The US is a bit weird. Some companies, like the one I work for, use Sun-Sat for the week and some use Mon-Sun for the week. I even worked for one company that used Fri-Thu for the week.

I also worked for a company that used Mon-Sun for the week except for the first week and last week, which could be "short weeks" of anything from 1 to 7 days depending on where the first day and last day of the year fell.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 15

robert.sterbal 56890 (10/23/2015)

On a related note:

What is the ISO standard for midnight?

2015-03-04 23:59

2015-03-0? 00:00

2015-03-05 00:01

Does ? = 4 or 5

To the best of my knowledge, I know of no temporal system that considers midnight to be at the end of the day. It always marks the beginning of a day. For example, there is no 24:00 for time even in the military.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions