SAP Knowledge Base Article - Public

1457614 - ‘DatePart’ function returns wrong value in Crystal Reports designer

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

  1. Create a Crystal Report
  2. Create a formula and use DatePart function as below

 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

SAP Knowledge Base Article 1394004

Keywords

KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , Bug Filed

Product

Crystal Reports 2008 V0 ; Crystal Reports 2008 V1 ; SAP Crystal Reports XI ; SAP Crystal Reports XI R2