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

• 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.

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

• 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.

• 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?

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

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