## ISO Weeks

## ISO Weeks

(OP)

I'd like to see some code for finding ISO Weeks :)

I have a stored proc here at work that runs the fairly standard Microsoft example of how to get an ISO weeks with lots of DatePart goodness and multiple lines...

Lets see who can make the:

Shortest line count, any language (not counting function declaration)

Shortest character count, any language (not counting function declaration)

Most efficient SQL Proc that may end up replacing the one we have now since I don't have the time to play with it myself :)

Information on ISO Weeks: http://www.phys.uu.nl/~vgent/calendar/isocalendar.htm

-T

I have a stored proc here at work that runs the fairly standard Microsoft example of how to get an ISO weeks with lots of DatePart goodness and multiple lines...

Lets see who can make the:

Shortest line count, any language (not counting function declaration)

Shortest character count, any language (not counting function declaration)

Most efficient SQL Proc that may end up replacing the one we have now since I don't have the time to play with it myself :)

Information on ISO Weeks: htt

-T

## RE: ISO Weeks

Function getISOweek(prmDate As Date) As Integer

getISOweek = DatePart("ww", prmDate, 2, 2)

End Function

Hope This Helps, PH.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

## RE: ISO Weeks

## RE: ISO Weeks

In my example I used firstdayofweek=2 (vbMonday) and firtweekofyear=2 (vbFirstFourDays), so where is the difference with ISOweek ?

## RE: ISO Weeks

But it's still cheating to use a built-in function, where's the fun in that? :D

## RE: ISO Weeks

used to VBScripthttp

## RE: ISO Weeks

Try it with 29th December 2003 or 31 December 2007 ...

## RE: ISO Weeks

Seems to work for 1st week but not for last ...

Yet another MS subtility.

## RE: ISO Weeks

## CODE

@finddate datetime

AS

SET DATEFIRST 1

DECLARE @seed datetime

SET @seed = (SELECT DATEADD(yy,DATEDIFF(yy,'1/1/2000',@finddate),'1/1/2000'))

SELECt DATEPART(yy,

DATEADD(yy,

SIGN(-1*(SIGN(DATEPART(dy,DATEADD(dd,-1,DATEADD(yy,1,@seed))) - DATEPART(dy,@finddate) -3 + DATEPART(dw,@finddate))-1)) +

SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1)

,@finddate)),

52 + (-1*SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1))*(DATEPART(dw,@seed)-4) +

(-1*SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1))*(DATEPART(dw,@seed)-5)*(DATEPART(dy,DATEADD(dd,-1,DATEADD(yy,1,@seed)))-365)+

51*SIGN(SIGN(DATEPART(dy,DATEADD(dd,-1,DATEADD(yy,1,@seed))) -DATEPART(dy,@finddate)-5 + DATEPART(dw,@finddate))-1),

@finddate;

EXEC ISODates '12/29/2009'

EXEC ISODates '1/1/2010'

EXEC ISODates '1/4/2010'

based on this

http://www.personal.ecu.edu/mccartyr/ISOwdALG.txt

I think the year works, just having problems with week 1 and 53 things.

## RE: ISO Weeks

## CODE

@finddate datetime

AS

SET DATEFIRST 1

DECLARE @seed datetime

DECLARE @firstdayyear datetime

SET @seed = (SELECT DATEADD(yy,DATEDIFF(yy,'1/1/2000',@finddate),'1/1/2000'))

SET @firstdayyear = (SELECT DATEADD(yy,DATEDIFF(yy,'1/1/2000',DATEADD(yy,

SIGN(-1*(SIGN(DATEPART(dy,DATEADD(dd,-1,DATEADD(yy,1,@seed))) - DATEPART(dy,@finddate) -3 + DATEPART(dw,@finddate))-1)) +

-1*SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1) * -1*(SIGN(SIGN(DATEPART(dw,@seed)-4)-1)+1)

,@finddate)),'1/1/2000'))

SELECt

DATEPART(yy,@firstdayyear),

(1+52*(-1*SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1))*(DATEPART(dw,@seed)-4) +

52*(-1*SIGN(SIGN(DATEPART(dy,@finddate) -8+DATEPART(dw,@seed))-1))*(DATEPART(dw,@seed)-5)*(DATEPART(dy,DATEADD(dd,-1,DATEADD(yy,1,@seed)))-365)

)

*((DATEPART(yy,@firstdayyear)- DATEPART(yy,@finddate))

*-1*SIGN(SIGN((DATEDIFF(dd,@firstdayyear,DATEADD(dd,-1,DATEADD(yy,1,@firstdayyear)))+1) - DATEPART(dy,@finddate) -3 + DATEPART(dw,@finddate))-1)+

-1*((DATEPART(yy,@firstdayyear)- DATEPART(yy,@finddate))-1)*

((DATEPART(dy,@finddate) + 7 - DATEPART(dw,@finddate) + DATEPART(dw,@seed) -1)/7)+

-1*(SIGN(SIGN(DATEPART(dw,@seed)-4)-1)+1))

;

EXEC ISODates '12/28/2008';--2008/52

EXEC ISODates '12/29/2008';--2009/1

EXEC ISODates '1/1/2009';--2009/1

EXEC ISODates '12/27/2009';--2009/52

EXEC ISODates '12/28/2009';--2009/53

EXEC ISODates '1/1/2010';--2009/53

EXEC ISODates '1/4/2010';--2010/1

Until someone breaks it of course.

## RE: ISO Weeks

I checked it against the tables in the ISO link provided in the first post. All test results were correct.

FUNCTION ISOWEEK

PARAMETERS qDate

PRIVATE ALL LIKE z*

zyr = YEAR(IIF(EMPTY(PARAMETERS()), DATE(), qDate))

zdays = qDate - WK1DATE(zyr)

zweek = INT(IIF(zdays < 0, qDate - WK1DATE(zyr - 1), zdays)/7)

RETURN IIF(zweek < 52, zweek, IIF(zyr = YEAR(WK1DATE(zyr + 1)), 0, 52)) + 1

FUNCTION WK1DATE

PARAMETERS qYear

PRIVATE ALL LIKE z*

zdate = CTOD('01/01/' + RIGHT('0000' + ALLTRIM(STR(qYear)), 4))

RETURN zdate - DOW(zdate) + IIF(DOW(zdate) < 6, 2, 9)

Examples:

?ISOWEEK()

28 This is the ISO week for today July 12, 2006

?ISOWEEK({12/31/2009})

53

I don't see how I can shorten it any more. Since I think this could be a quite useful function in the FoxPro community, I am also writing a FAQ for the FoxPro folks.

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

Oops. I found a bug in my last post.

Please change:

zyr = YEAR(IIF(EMPTY(PARAMETERS()), DATE(), qDate))

TO:

qDate = IIF(EMPTY(PARAMETERS()), DATE(), qDate)

zyr = YEAR(qDate)

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

## CODE

GetISOWeek = DateSerial(Year(aDate) + (Month(aDate) = 1 And Day(aDate) + 3 - ((WeekDay(aDate) + 5) mod 7) <= 0),1,1)

GetISOWeek = (Round((DateDiff("d",GetISOWeek,aDate) + (((WeekDay(GetISOWeek)+1) mod 7) - 3) + (7 - ((WeekDay(aDate) + 5) mod 7)))/7,0) -1) * ((Month(aDate) = 12 And Day(aDate) + (7 - (WeekDay(aDate) + 5) mod 7) > 35) + 1) + 1

End Function

2 Lines without the linewrap. I suppose I could have simply pasted the first line into the two places I needed it in the second line, but that felt like breaking the rules.

I checked it for the next 50 years against a VBA function I found (that turned out to start weeks on Sundays instead of Mondays) and I checked it against DatePart("ww",d,2,2) (which has 4 weeks in the next 50 years that are exactly 1 day long - but everything else matched).

If I had to make a more efficient version I would probably assign the weekday(), month(), and year() calls to variables, but leave the majority of the math alone.

-T

## RE: ISO Weeks

Tarwn

To check validity of your function, or any ISO week function, one needs to only check one year for each of the 15 possible ISO years as outlined in the link in your original post. Using a scatter-gun approach, one only needs to check each year from 2001 thru 2028, which time frame includes all varieties of ISO years at least once.

The hardest days to get correct are from 27Dec thru 4Jan of any year - the rest of the dates are straight-forward math.

One thing you did not do is to note which language you used in your function. It is obviously not FoxPro, nor BASIC, and I have no idea which language it is.

I could have put all of my code in two lines also, but they would be much longer than yours. To do so I would have sacrificed readability, as well as sacrificing a second function which determines the beginning date of any ISO year. Of course, if I had, I would have again posted some of the most UGLY code I have ever written as I did once before for you in a previous thread.

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

A longer version would look like:

## CODE

Dim isoYear, stDaysAdjust, enDaysAdjust, numDays, numWeeks

Dim adWeekDate, adDay

adWeekDay = Weekday(aDate)

adDay = Day(aDate)

'--- quick escape for december days that belong to week 1 of following year

If Month(aDate) = 12 And _

(adDay = 29 And (adWeekDay = 1 Or adWeekDay > 5)) Or _

(adDay = 30 And (adWeekDay = 1 Or adWeekDay = 6)) Or _

(adDay = 31 And adWeekDay = 1) Then

GetISOWeekLong = 1

Exit Function

End If

'--- calculate what year we are working with

If Month(aDate) = 1 And adDay < 3 Then

'previous year

isoYear = DateSerial(Year(aDate) - 1,1,1)

Else

'current year

isoYear = DateSerial(Year(aDate),1,1)

End If

'--- calculate number of days since beginning of year

numDays = DateDiff("d",aDate,isoYear)

'--- Additional adjustment days - num days back to monday and

' forward to monday from year start and passed date

'stDaysAdjustment - Some close enough modifications to remap MS weekday range and get number of days since previous Monday

stDaysAdjust = ((Weekday(isoYear) + 1) Mod 7) - 3

'enDayAdjustment - remap MS weekday range and get number of days until following Monday

enDaysAdjust = 7 - ((adWeekDay + 5) Mod 7)

'--- determine number of weeks

numWeeks = Round((numDays + stDaysAdjust + enDaysAdjust)/7)

GetISOWeekLong = numWeeks

End Function

This time I created variables for re-used functions, removed most of the more confusing math, and added comments.

I try to write the shortest ones as a challenge to make me think of new ays to work on problems. I would never do something like that in code that was going into production software. But it did give mean idea on a much shorter/efficient ISO Week method for the SQL proc I originally mentioned.

## RE: ISO Weeks

I think I will rewrite my code into to real ugly code and see if I really can get it in two lines as I think I can.

As far as using that ugly code, I don't anymore. When I was writing in Apple BASIC on an Apple II+, I was forced to minimize all code at the expense of readability, so today it is real easy for me to take other code of hundreds of lines and sometimes pack it all into only 3 lines. A weakness on my part, as I tend to want to use that same code in my programs, even though others would never be able to maintain it.

Anyhow, I will be back with a condensed version of this in a few days. And it will be in fewest lines possible subject to the maximum line length in FoxPro.

I have a question though. Since I have no way to test your script, what date range is it valid for? Even though our calendar was not used 2000 years ago, my function will work for any 1, 2, 3, or 4 digit year, in other words from about 1AD thru 9999AD with the assumption that our calendar was valid then.

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

One thing I did differantly in mine than some of the others, in order to save a little space, is that instead of changing the beginning of year date to the first monday, I just went ahead and got the difference and added it into the mix. It saved me an additional dateadd. Same thing with the final day, rather than move it to the next monday I just added the extra days. The Round is there because I got lazy and Sunday gets 0 added instead of 1, causing a need for a round up.

Another one was changing theyear of my start date based on a boolean statement. Since VBScript returns a -1 for true I basically built a short boolean statement to determine if we were in dec 28,29,30 on fri, sat, sun.

This: WeekDay(aDate) + 5) mod 7

Is actually a fairly quick way to remap the MS Sunday=1 through Sat=7 weekdays to ISO Monday=0 through Sun=6

Something similar showed up ina couple plaxces in my code, but thats only because I was bulding it one piece ata time and didn't realize I came to the same conclusion several times :P

Anyways, off to work on the Graffiti package for a whle, before my PHP atrophies.

## RE: ISO Weeks

Anyone know how I could find the number of weeks in a year beginning with the weeks beggining on a saturday. I want to just pass the year number in.

Cheers

## RE: ISO Weeks

A modification of my code should produce whichever you are looking for. There probably is some code out there somewhere that already does what you want.t

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

## CODE

[code]

is returning the ISO week number. So it's a built in function. You may beat that only with a language having ISO weeks as it's default for some builtin function ISOWeek(dDate).

But it's flexible, as you may assume from the 2 parameters additional to the Date. Let's take a look at the definition:

1. Parameter (nFirstWeek):

Can be one of4 values from 0 to 3 with these meanings:

0: Specifies that WEEK( ) return the week that is currently selected in the First Week of Year box on the Regional tab of the Options dialog box (of Windows).

So this one is letting the user choose the first week definition at the OS level.

Not necessarily ISO weeks.

1: First week contains January 1st. This is the default when you omit nFirstWeek.

Also not ISO week definiton.

2: The larger half (four days) of the first week is in the current year.

This is equivalent to the condition, that the thursday belongs to the first week. As that means that Thursday to Sunday belong to the first week and it's at least 4 days long.

So this IS ISO weeks in that condition.

3 First week has seven days.

Definately not ISO weeks

2. Parameter (nFirstDayOfWeek):

0: Specifies that WEEK( ) return the day that is currently selected in the Week Starts on box on the Regional tab of the Options dialog box.

So this again let's the user decide at OS level.

1 Sunday. This is the default when you omit nFirstDayOfWeek and is the first day of the week used in earlier FoxPro versions.

2 Monday

3 Tuesday

4 Wednesday

5 Thursday

6 Friday

7 Saturday

Therefore ISOWEEK(dDate) is identical to Foxpros Week(dDate,2,2).

The default week(dDate) (those additional parameters are optional) returns, as you can see from the description the week numbered with the weekstart on Sundays and 1st week contains January 1st. This is US standard of week numbering.

Despite of the advantage of the extreme shortness, that function is of course not easily portable to other languages.

proof of correctness can be obtained by the output of this:

[code]

? week(Date(2008,12,29),2,2)

* 1

? week(Date(2009,1,4),2,2)

* 1

? week(Date(2009,12,28),2,2)

* 53

? week(Date(2010,1,3),2,2)

* 53

Bye, Olaf.

## RE: ISO Weeks

Bye, Olaf.

## RE: ISO Weeks

Olaf

The WEEK() function does not exist in FP 2.6 so I can not use it to find any of the many ways to calculate any part of the week. I have no idea when it was first included in FP, so it may not be an option for many people.

Quote from above:

And finally, even if WEEK() was available in FP 2.6, I don't see how it could easily be used to find the first day of the ISO year whereas the WK1DATE() function I wrote above returns the first day of any ISO year when passed the year in question, i.e. 2006 or whatever.

Even though I can't use it, just the fact that you have enlightened us with another obscure, but very useable function, earns you a star.

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

FP 2.6 is very old and not even supported anymore. The Week() function is this way as long as I know VFP, that startet with VFP6. So very many Foxpro users can make use of it, as most of them nowadays use VFP8 or 9.

First day of the iso year? Well, compute the week of the 1.1. of the year in question. if it's the 1. week of that wear, subtract the difference to monday, if it's >1 then add the difference in days to monday. Easy.

Bye, Olaf.

## RE: ISO Weeks

Bye, Olaf.

## RE: ISO Weeks

Ja, FP 2.6 is old and unsupported, but some of us, including me, are stuck with it for several reasons. One, many existing working programs are not cost-effective to rewrite. Two, anyone using a Mac or Unix system cannot upgrade to later versions of FP. Three, at my age, I see no point in learning OOP as the learning curve is longer than my estimated working life.

Since FP 2.6 will exist long after I die, it makes more sense for me continue with what I 'know' (ha-ha-ha), than to migrate to a technology that I will never have a chance to master. So I will continue with the old, and when I see something in the new that will help with the old, I will hijack it for use with the old.

mmerlinn

"Political correctness is the BADGE of a COWARD!"

## RE: ISO Weeks

that's okay with me. But you said it may not be an option for many people. I couldn't let that stand uncommented.

It may be an option for few, as vfp is not very spread, still it's rising high in the tobe index of programmer communities: http://www.tiobe.com/tiobe_index/index.htm,

currently ranking 14th right after Ruby and before VB.net.

Bye, Olaf.

## RE: ISO Weeks

## CODE

Dim D2 As Date

D = D - (D - 2) Mod 7 + 3

D2 = DateSerial(Year(D), 1, 1)

D2 = D2 + 6 - (D2 - 6) Mod 7

ISOWeek = (D - D2) \ 7 + 1

End Function

To compete for shortest code, I'll eliminate variables or types to get 147 characters, not counting carriage returns or indent spaces:

## CODE

D = D - (D - 2) Mod 7 + 3

D2 = DateSerial(Year(D), 1, 1)

D2 = D2 + 6 - (D2 - 6) Mod 7

ISOWeek = (D - D2) \ 7 + 1

End Function

Getting it on three lines is easy: just put colons between statements. :)

Here's a SQL Server version for your pleasure.

## CODE

RETURNS int

AS

BEGIN

DECLARE @D int, @D2 int

SET @D = Convert(int, DateAdd(dd, DateDiff(dd, 0, @TheDate), 0))

SET @D = @D - @D % 7 + 3

SET @D2 = Convert(int, DateAdd(yy, DateDiff(yy, 0, DateAdd(dd, @D, 0)), 0))

SET @D2 = @D2 + 6 - (@D2 - 4) % 7

RETURN (@D - @D2) / 7 + 1

END

I'll be interested to see if I made any mistakes!

Erik

## RE: ISO Weeks

to somehow really earn the star, here's my vfp solution, not using some special vfp function like week() or dow() (day of week), just using Year(), returning the year of some date, Date(), which is equal to VBs DateSerial() and using date differences, which in vfp can simply be calculated by subtracting two variables or expressions of the date type. These operations should be available in any programming language or be implemented easily.

I also use two constant dates: 31st december 1999 is a friday therefore 3rd january 2000 is a monday. Those could be replaced by any monday or friday. Or even substitute expressions like (date-dateconstant)%7 through some dayofweek function, that returns values from 0 to 6, 0 being the same weekday as the dateconstant.

## CODE

Local ldLastmonday, ldNewyear, ldFirstMonday

* monday of the same week as tdDate:

ldLastmonday = tdDate-(tdDate-Date(2000,1,3))%7

* newyear of the year of the thursday of the same week:

ldNewyear = Date(Year(ldLastmonday+3),1,1)

* first monday of that isoyear:

ldFirstMonday = ldNewyear+3-(ldNewyear-Date(1999,12,31))%7

* isoweek:

Return Int((ldLastmonday-ldFirstMonday)/7)+1

EndFunc

That can be shortened by using short var names and substituting ldNewyear into the line computing the first monday (f):

## CODE

l = d-(d-Date(2000,1,3))%7

f = Date(Year(l+3),1,1)+3-(Date(Year(l+3),1,1)-Date(1999,12,31))%7

Return Int((l-f)/7)+1

EndFunc

Bye, Olaf.

## RE: ISO Weeks

Our solutions are basically identical in their method... interesting. Date arithmetic is convenient!

I didn't think about the fact that I could use other days besides Thursday for the week day comparison part, so I thought at first that my function might be able to drop an operation by trying other days until an operand comes out to be 0. But then I realized that by using Monday instead of Thursday, you had to add +3 Year(l+3)... hmmm.

You probably know this, but you could simplify by changing

Date(2000,1,3)

and

Date(1999,12,31)

to their integer values modulo 7, whatever that comes out to in VFP. Of course this makes porting to other languages more difficult, but is more optimized for the specific language.

## RE: ISO Weeks

yes, quite the same idea.

hmm, yes, to compute the weeks thursday in the first place, instead of the weeks monday I could drop the +3 for computing the iso years new year day, true. int((thisweeksthursday-firstmondayofisoyear)/7)+1 would still be the correct return value.

In vfp you cannot compute SomeDate()%7. The date type surely is some number internally, but vfp does not allow this kind of expression. I first have to compute a difference between two dates in days, then can take modulo 7 from that.

However vfp has a julian day function. It's a bit ugly, as it returns the julian day number as a string, might still be faster to compute (Val(sys(11,somedate))+n)%7 instead of (somedate-constantdate)%7.

But as said I could also simply use the day of week function DOW(), returning 1 to 7 for whatever day I determine to be the first weekday, I intentionally not used that function. I thought a date difference might be a more basic calculation you could translate to other languages.

Bye, Olaf.

## RE: ISO Weeks

And thats why I like these kinds of problems, they make us stretch our intellect to continually simplify the complex until you end up with a very complex simplification :)

## RE: ISO Weeks

Bye, Olaf.