DatePart function returns incorrect week number

[consultfm]

Hi,
I'm having a problem getting the correct week number for the date 31st December 2001 in Crystal Reports 8.0. Using Monday as first day of the week and the week containing Jan 1st as first week of the year, my formula:

DatePart("ww", #December 31, 2001#, crMonday, crFirstJan1)

returns 53.00 when I expected 1.00

Can anyone out there explain what is going wrong here, or suggest another method?

Cheers.

 

[synapsevampire]

There are many ways that workweeks are computed.

There's an ISO Standard, there's SQL Servers workweeks which differ... etc.

Since you explicitly said that the workweek begins on a Monday, and that date is a monday still in the year 2001, it seems fitting that it be week 53.

Perhaps you want something akin to the ISO, which would use:

crFirstFourDays: Start with the first week that has at least four days in the new year.

Or perhaps you do NOT want the ISO nor SQL Server method, rather you want to compute a workweek based on the number of the days in a year/7:

Use:

int(datepart('y',currentdate)/7)

Hope one of these get you there.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[consultfm]

Kai,
Thanks for the feedback on DatePart problem.
I know that in oracle pl/sql I can use

TO_CHAR(ACT.STARTDATUM , 'IW'))

to get the correct result i.e. week number 1. I think this uses ISO and 'IW' means 'international week'. I'll give your ideas a go.
Cheers.