DatePart and week 2007/53 ?

[paulapl]

Hey,

I'm using formula

totext (DatePart ("yyyy",{table.datetime}, crMonday, crFirstFourDays),0,"") + "/" + totext (DatePart ("ww",{table.datetime}, crMonday, crFirstFourDays),"00")

as group on

and it creates week 53 to year 2007!

why?

 

[beltmanjr]

What can I say?
There really are 53 weeks to some years. You can actually count this yourself on a calendar.

Monday 31 Dec is one of those days that is the first and last day of week 53 (assuming your week starts on Monday).

If you like that to be displayed as week 1 of the next year you could check on the week number being larger than 52 and simply add a year to the year you extracted and set the week to 01.

 

[jlumley]

While it is true that there are 53 weeks to some years depending on the first day of the week and what constitutes the first week of the year, the situtation laid out above should not have a week 53.

A week has to include seven days so showing Dec 31 as week 53 then showing Jan 1 as week 1 is incorrect since Dec 31 is supposed to be the first day of the week, in this case. You cannot have ONE DAY be the first and last day of an entire week.

In other languages I've had experience programming in return Dec 31 - Jan 6 as week 1 of 2008 and Dec 24 - Dec 30 as week 52 of 2007.

The only reason I can give for why it is creating a week 53 is that the formula is implemented incorrectly. It looks like it is using the first day of the week as a higher priority for calculating the week number than the argument for what makes the first week of the year.

 

[lbass]

jlumley,

Actually it is not incorrect. The week formula just designates the week the date is in, not the 7-day interval itself.

-LB

 

[beltmanjr]

And this happens with many more reporting tools.

anyway, fix it by:

NumberVar yearnumber := DatePart ("yyyy",{table.datetime}, , crMonday, crFirstFourDays);
NumberVar weeknumber := DatePart ("ww",{table.datetime}, , crMonday, crFirstFourDays);

if weeknumber=53 then
(
  weeknumber := 1;
  yearnumber := yearnumber+1;
);
totext (yearnumber,0,'') & '/' & totext (weeknumber,"00");

 

[jlumley]

That works until you get a year that acutally has 53 weeks in it.

I think the OP's concern is that for businesses that conform to ISO 8601 (start week 1 of the year as the first week containing four days and start weeks on Mondays), 2007 does not have 53 weeks. The next year that has 53 weeks isn't until 2009.

 

[beltmanjr]

ISO 8601 compliant (thanks for pointing this out jlumley)

NumberVar yearnumber := DatePart ("yyyy",{table.datetime}, crMonday, crFirstFourDays);
NumberVar weeknumber := DatePart ("ww",{table.datetime}, crMonday, crFirstFourDays);

if weeknumber=53 and DayOfWeek (cdate(yearnumber+1,1,1), crMonday) < 5 then
(
    
    weeknumber := 1;
    yearnumber := yearnumber+1;
);
totext (yearnumber,0,'') & '/' & totext (weeknumber,"00");

 

[beltmanjr]

Ok, the above formula is wrong as it didnt take into account what happens with the first couple of days of the new year. Correction below for ISO 8601:

NumberVar yearnumber := DatePart ("yyyy",{table.datetime}, crMonday, crFirstFourDays); // use {table.datetime} here
NumberVar weeknumber := DatePart ("ww",{table.datetime}, crMonday, crFirstFourDays); // use {table.datetime} here

// Date falls in week 53 and weekday of 1 jan next year is smaller than 5 (meaning week 53 has less than 4 days)
// Make it week 1 of the next year
if weeknumber=53 and DayOfWeek (cdate(yearnumber+1,1,1), crMonday) < 5 then
(
    weeknumber := 1;
    yearnumber := yearnumber+1;
)
// Week is in week 1 or a week higher then 53 (happens with the first days where week 53 of the previous year has 4 days)
// And extra check that indeed the last week of the last year had at least 4 days and that the input date is weekday 5 or larger
else if (weeknumber=1 or weeknumber>53) and DayOfWeek (cdate(yearnumber-1,12,31), crMonday) > 3 and DayOfWeek ({table.datetime}, crMonday) > 4 then // use {table.datetime} here
(
    weeknumber := DatePart ("ww",cdate(yearnumber-1,12,31), crMonday, crFirstFourDays);
    yearnumber := yearnumber-1;
);

totext (yearnumber,0,'') & '/' & totext (weeknumber,"00");