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