SAP Knowledge Base Article - Public

1263435 - How to convert a Julian Date to a Date, or a Date to a Julian Date in Crystal Reports?

Symptom

  • How to convert a Julian Date to a Date?
  • How to convert a Date to a Julian Date in Crystal Reports?
  • Cannot find functions to convert a Date to Julian, or a Julian Date to Date in Crystal Reports.

Environment

  • SAP Crystal Reports 2013
  • SAP Crystal Reports 2016 
  • SAP Crystal Reports 2020

Cause

  • Julian dates are a continuous count of days from January 1, 4713, before the current era, on the Julian calendar.
  • There is no functions in Crystal Reports to convert Julian Date, to calendar date, or vice-versa.

Resolution

  • To convert a Julian Date to a Date, or a Date to a Julian Date in Crystal Reports, create a formula.
    Below are example of formulas:
      
  • To convert a Julian Date, to a Date:

    Local StringVar julianDate := <ENTER A NUMERIC CHARACTERS JULIAN DATE HERE>;

    Local NumberVar Q := If isNumeric(julianDate) Then ToNumber(julianDate) + 0.5;
    Local NumberVar Z := Int(Q);
    Local NumberVar W := Truncate((Z - 1867216.25)/36524.25,0);
    Local NumberVar X := Truncate(W/4,0);
    Local NumberVar A := Z+1+W-X;
    Local NumberVar B := A+1524;
    Local NumberVar C := Truncate((B-122.1)/365.25,0);
    Local NumberVar D := Truncate(365.25 * C,0);
    Local NumberVar E := Truncate((B-D)/30.6001,0);
    Local NumberVar F := Truncate(30.6001 * E,0);
    Local NumberVar myDay := Truncate(B-D-F+(Q-Z),0);
    Local NumberVar myMonth := Truncate(If E-1 <= 12 Then E-1 Else E-13,0);
    Local NumberVar myYear := Truncate(If myMonth in [1,2] Then C-4715 Else C-4716,0);

    If myYear <= 9999 and myMonth in 1 to 12 and myDay <= 31 Then
         Date(myYear,myMonth,myDay)
    Else
         Date(0,0,0)
        

  • To convert a Date to a Julian Date

    Local DateVar myDate := <ENTER A DATE HERE>;

    Local NumberVar myYear := Year(myDate);
    Local NumberVar myMonth := Month(myDate);
    Local NumberVar myDay := Day(myDate);

    If myMonth in [1,2] Then
    (
       myYear := myYear - 1;
       myMonth := myMonth + 12;
    );

    Local NumberVar A := Truncate(myYear/100,0);
    Local NumberVar B := Truncate(A/4,0);
    Local NumberVar C := 2-A+B;
    Local NumberVar E := Truncate(365.25 * (myYear + 4716),0);
    Local NumberVar F := Truncate(30.6001 * (myMonth + 1),0);

    Round(C + myDay + E + F - 1524.5,0);

      
  • Examples:
    • The date 2001/10/28 will convert to the Julian Date numeric value of: 2452211
    • The Julian Date: 2452211 will return the date 2001/12/28, which is 2,452,211 days from January 1, 4713 B.C.
              
  • Note:
    • If you are often using the same formula, a suggestion is to convert the formula in a Custom Function.
    • For more information on Custom Function, see the SAP Knowledge Base Article:   
      1217663 - How to create a Custom Function in Crystal Reports?

Keywords

CR, JD , KBA , BI-RA-CR , Crystal Reports designer or Business View Manager , How To

Product

SAP Crystal Reports 2013 ; SAP Crystal Reports 2016 ; SAP Crystal Reports 2020