Symptom
The 'Datepart' function does not return the correct value at the end of the year when output is number of weeks. It returns a value 9362 or 9363 instead of week no 52 or 53.
Environment
Crystal Reports 2008
Reproducing the Issue
datepart('ww',{date_table.date},crMonday,crFirstFourDays) Where {date_table.date} is a database field with some date values as shown in below table
date ({date_table.date}) |
Ouput (Week number) |
Result |
31-Dec-09 |
53 |
correct |
1-Jan-10 |
9362 |
wrong |
4-Jan-10 |
1 |
correct |
31-Dec-10 |
52 |
correct |
1-Jan-11 |
9363 |
wrong |
2-Jan-11 |
9363 |
wrong |
3-Jan-11 |
1 |
wrong |
Cause
This problem is identified as a potential defect in the product. It is currently under review by Product Group under reference number ADAPT01406209.
Resolution
This is not considered by product group with following explanation.
The formula is datepart('ww'{date_table.date},crMonday,crFirstFourDays) in this report. Please note that the last argument is crFirstFourDays. This argument means start with the first week that has at least four days in the new year. For example, 1/1/2010 of that week has only three days (1/1/2010, 1/2/2010, 1/3/2010) in 2010. So date 1/1/2010 is not first week in 2010, it is also not 52nd or 53rd week in 2009. The function will return large number. In this case, the date 1/4/2010 is the first week in 2010.’
Workaround:
Use one of the following two formulas:
Formula1:
if datepart('ww',{date_table.date},crMonday,crFirstFourDays) >53
then datepart('ww',{date_table.date}-5) else datepart('ww',{date_table.date},crMonday,crFirstFourDays);
Forumula2:
if datepart("ww",datum,crMonday, crFirstFourDays)=9363 then
datepart("ww",datum-7,crMonday, crFirstFourDays)+1
else
datepart("ww",datum,crMonday, crFirstFourDays);
See Also
Keywords
KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed