ISO Weeks 2

[Tarwn]

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

 

[PHV]

In VBA:
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

 

[Tarwn]

I'm fairly certain Microsoft week are not the same as ISOWeeks. The first Microsoft week of the year is the week that includes Jan 1. This is not necesarally the case with ISO Weeks

 

[PHV]

Tarwn, in the debug window type datepart and press the F1 key ...
In my example I used firstdayofweek=2 (vbMonday) and firtweekofyear=2 (vbFirstFourDays), so where is the difference with ISOweek ?

 

[Tarwn]

Ah, my fault, used to VBScript and T-SQL, I try to stay away from standard VB and VBA

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

 

[PHV]

used to VBScript

http://msdn.microsoft.com/library/en-us/script56/html/4e45cd84-b22b-437f-8410-6f4b8ff1c769.asp

 

[strongm]

>so where is the difference with ISOweek ?

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

 

[PHV]

Damned !
Seems to work for 1st week but not for last ...
Yet another MS subtility.

 

[pwilson]

This is close. It messes up on the week for 12/29/2009, 1 instead of 53.

ALTER PROCEDURE ISODates
    @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.

 

[pwilson]

Ok this looks like it works

ALTER PROCEDURE ISODates
    @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.

 

[mmerlinn]

Ok folks. Here is my submission using FoxPro.

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


[TT]
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)
[/TT]

Examples:

[TT]
?ISOWEEK()
[COLOR=BLACK YELLOW]28[/COLOR] This is the ISO week for today July 12, 2006

?ISOWEEK({12/31/2009})
[COLOR=BLACK YELLOW]53[/COLOR]
[/TT]

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!"

 

[mmerlinn]

Oops. I found a bug in my last post.

Please change:

[tt]
zyr = YEAR(IIF(EMPTY(PARAMETERS()), DATE(), qDate))
[/tt]

TO:

[tt]
qDate = IIF(EMPTY(PARAMETERS()), DATE(), qDate)
zyr = YEAR(qDate)
[/tt]




mmerlinn

"Political correctness is the BADGE of a COWARD!"

 

[Tarwn]

Ok, I've been working on this all day, here is my entry:

Function GetISOWeek(aDate)
	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

 

[mmerlinn]

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!"

 

[Tarwn]

The language was VBScript and the goal I had when writing that was to make it the absolutely shortest function I could without explcitly duplicating any code or use any implicit or explicit line continuations.

A longer version would look like:

Function GetISOWeekLong(aDate)
	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.

 

[mmerlinn]

[grin]

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!"

 

[Tarwn]

Hmm. Good question. I know I tested it for the next 50 years, but it's probably not good that far back since I'm using an MS product.

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

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

 

[stuartgmilton]

Guys,

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

 

[mmerlinn]

Do you need the number of FULL weeks in a year or the number of weeks beginning with Saturday even if only partial weeks in that year (The number of Saturdays in a year)?

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!"

 

[Olaf Doschke]

mmerlin, it's much simpler in Foxpro:

   Week(Date(),2,2)
[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.