Print Page | Close Window

Julian Date to DD/MM/YYYY

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=792
Printed Date: 29 Apr 2024 at 7:57am


Topic: Julian Date to DD/MM/YYYY
Posted By: Querida
Subject: Julian Date to DD/MM/YYYY
Date Posted: 06 Jun 2007 at 12:27am
Hi all,
 
Can anyone tell me how I can translate a Julian date to a regular DD/MM/YYYY date?
For example: date stored as 107.157 needs to be displayed as 06/06/2007.
 
Thanks in advance,
Querida


Replies:
Posted By: hilfy
Date Posted: 06 Jun 2007 at 8:37pm

There is a Julian Date user function library available on the Business Objects website - http://technicalsupport.businessobjects.com - http://technicalsupport.businessobjects.com

Do a search for "ufljul.exe"
 
-Dell


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics

Posted By: Querida
Date Posted: 07 Jun 2007 at 12:52am

Thanks for your replay.

Unfortunately the function you mentioned isn’t what I was looking for. It can be used to convert ‘real julian dates’ (starting from January 1, 4713 B.C) but not the mathematical julian dates which are stored in our database.

Is there another existing function which converts these dates or can someone let me know how to create a function since I'm going to need this quit often.

Posted By: BrianBischof
Date Posted: 07 Jun 2007 at 11:36am
I don't get it. Your julian dates are different from the standard julian dates? That sounds strange. Secondly, if your julian dates are unique to your company, then it would be tough for someone here to tell you how convert them. I found a thread on ExpertsExchange.com about this but you have to be a member to read the answers. So I copied a couple replies here. I have no idea if this will help you, but it should get you in the right direction.
==============================
This is a really easy formula to write, but I need to know the year for the sample dates you posted.
The number you posted first, is the number of days from a given date.  I need to determine that date to be able to supply you the formula.
So, can you give me the years for the sample date and expected outcome that you supplied?

Number    Expected Outcome
58829      Jan 24
57607      sept 20
58178      apr 14
58034      nov 21

The month and day are useless without the year.
If I get the year, we can then subtract the number of days to get the starting date.
The formula in Crystal would then look like this if the starting date was 1/1/1900

dateadd("d",{table.field},date(1900,1,1)
)
========================
Using dateadd("d",{table.field},date(1899,12,3
1)) yields the correct dates but the year is needed.

If you know the year for each date then you can use

dateadd("d",-{table.field},date(YEAR,1,24)) to determine the start point


-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>

Posted By: Querida
Date Posted: 07 Jun 2007 at 11:44pm
Sorry if I didn't explain these julian days properly.
I'll try to explain these julian dates with the example I gave in the beginning.
107.157 is June 6 2007
1 --> the century = 20
07 --> the year = 07
157 --> the day = June 6
 

Posted By: BrianBischof
Date Posted: 08 Jun 2007 at 12:13am
Alright. I decided to work on it before going to bed. Here is the formula I used to get this working. I convert it to a string and then parse out the parts of each date and convert them to numbers. Then I use the Date and AddDate functions to combine it into a normal date field.
numbervar JulianDate:=107.157;
stringvar DateString;
dateTimevar NewDate;

DateString := ToText(JulianDate,3);
NewDate:=Date (2000+ToNumber(Mid(DateString,2,2)),1 ,1 );
NewDate:=DateAdd("d", ToNumber(Mid(DateString,5, Length(DateString)-4))-1, NewDate);



-------------
Please support the forum! Tell others by linking to it on your blog or website:<a href="http://www.crystalreportsbook.com/forum/">Crystal Reports Forum</a>


Print Page | Close Window