1537984 - How to convert a date value to number format?

SAP Knowledge Base Article - Public

1537984 - How to convert a date value to number format?

Symptom

How to convert a date value to number format?

Environment

  • Crystal Reports XI R2 (All Patch levels)
  • Crystal Reports 2008 (All Patch levels)

Reproducing the Issue

  1. Create a Crystal Report against the Xtreme Sample Database
  2. Create a new formula (Field Explorer ->Right click ‘Formula fields’ ->New) as follows: 

          tonumber({Orders.Order Date})

        3.   When you try to save this formula it throws an error : ‘There is an error in this formula: A string is required here’

    Resolution

    1. Edit the same formula created above as follows:
    2. stringVar yyyy;
      stringVar mm;
      stringVar dd;
      numberVar result;

      if result=0 then
      (
      yyyy:=replace(replace(cstr(year({Orders.Order Date})),",",""),".00","");
      mm:=replace(cstr(month({Orders.Order Date})),".00","");
      if len(mm)<2 then mm:="0" & mm;
      dd:=replace(cstr(day({Orders.Order Date})),".00","");
      if len(dd)<2 then dd:="0" & dd;

      result := ToNumber(yyyy & mm & dd);
      );
      result;

    3. Save this formula and you can now use the variable ‘result’ in any other formula which requires date manipulations

    Keywords

    Convert Date to Number, Crystal Reports , KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , Problem

    Product

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