SAP Knowledge Base Article - Public

1206268 - How to convert Julian Date Values to a standard date format in the Crystal Reports Designer XIR2

Symptom


How to convert Julian Date Values to a standard date format in the Crystal Reports Designer XIR2

Cause

Unable to convert Julian Date Values to a standard date format in the Crystal Reports Designer XIR2

Resolution

  1. A true Julian date is the sequential count of days since noon on January 1, 4713 B.C. to the present date. The French classical scholar Joseph Justus Scaliger proposed this representation of date in 1583 as the true beginning of the Julian era.
  2. A more modern definition of Julian date that is used in the computer industry is a Julian Date equals the number of days from January 1st to a specified date in the same year. Such a Julian Date returns the number of days from the beginning of that year.
  3. To return a true Julian date, that is, the number of days from January 1, 4713 B.C. to a specified date, you can use the datetojulian and juliantodate functions.
  4. To obtain these functions, please visit http://support.businessobjects.com/search and search for the file name "UFLJUL.EXE". Install as per the instructions in the read me file.
  5. You can use the datetojulian function to convert a date into the number of days since January 1, 4713 B.C.
  6. For example: datetojulian (date(2001,10,28)) returns 2,452,211. That is, there are 2,452,211 days from January 1, 4713 B.C. to October 28, 2001 A.D.
  7. You can use the juliantodate function to convert a true Julian date value into a true date.

For example:

Juliantodate(2452211) returns 10/28/2001. That is, October 28, 2001 is 2,452,211 days from January 1, 4713 B.C.

To return the number of days since January 1 of the current year, the more modern Julian Date, write a formula similar to the following:

//@JulianDate

//This formula takes a

//date field from the database

//and subtracts it from the first

//day of the first month

//of the same year as the

//date field.

//It returns a numeric value

//that represents the number of

//days since the beginning of

//the year.

{date.field} - Date(Year({date.field}),1,1)+1

When you preview the report, this formula returns a numeric value representing the number of days from January 1st to the date field on the report.

For example:

If the database field is April 12, 2001 then the above formula returns 102 because there are 102 days from January 1, 2001 to April 12, 2001.

Similarly, you can use the "DayofYear" function to return the number of days from the beginning of a given year. This function is included with the file download "UFLDATE.EXE".

To obtain this file, please visit http://support.businessobjects.com/search and search for the file name "UFLDATE.EXE". Install as per the instructions in the read me file.

For example:

Dayofyear({date.field}) returns 33 if {date.field} is February 2, 2001 because there are 33 days from January 1, 2001 to February 2, 2001.

All the above methods take into consideration leap years.

For more information on Julian Dates and how they work in Crystal Reports, you can download a whitepaper at the following link:

http://support.crystaldecisions.com/docs

and search for the following file:

scr567_datetimefunctions.pdf


Keywords

KBA , BI-RA-CR , Crystal Reports designer or BusinessViews Manager , How To

Product

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