Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2011
    Posts
    2

    Unanswered: Problem with datepart function using date 01/01/2011

    We have an issue that we were wondering if anyone has come across before and if so how to fix it. We are using Crystal XI Version 11.5.10.1263

    datepart('ww',date('01/01/2011'),crMonday,crFirstFourDays) gives 9363
    datepart('ww',date('01/01/2011'),crMonday,crFirstFullWeek) gives 9363

    datepart('ww',date('01/01/2011'),crMonday,crFirstJan1) gives 1
    datepart('ww',date('01/01/2011'),crMonday,crUseSystem) gives 1

    datepart('ww',date('03/01/2011'),crMonday,crFirstFourDays) gives 1

    We are basically expecting the first 2 to return 53 as 01/01/2011 is in week 53 of 2010 according to our understanding of the ISO standard. This also happens for 02/01/2011. Is this an issue with Crystal?

    Any help/suggestions much appreciated. Thanks.
    Regards
    Richard Chambers
    Worldmark UK.

  2. #2
    Join Date
    Mar 2011
    Posts
    2
    I have found the following on the SAP website si it looks like it is a known issue.

    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
    0. Create a Crystal Report
    0. Create a formula and use DatePart function as below
    *datepart('ww',{date_table.date},crMonday,crFirstF ourDays)*
    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,crFirstFou rDays) 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,crFirstFo urDays) >53
    then datepart('ww',{date_table.date}-5) else datepart('ww',{date_table.date},crMonday,crFirstFo urDays);
    Forumula2:
    if datepart("ww",datum,crMonday, crFirstFourDays)=9363 then
    datepart("ww",datum-7,crMonday, crFirstFourDays)+1
    else
    datepart("ww",datum,crMonday, crFirstFourDays);

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •