JulianToDate Function

[giggles7840]

Does anyone know if the JulianToDate function is not compatible with XIR2?

Its returning values that look like 1/1/-4426.....

 

[giggles7840]

In case you are wondering... This field is brought in from as/400 into a universe and lands in crystal as a string field.

a value looks like this - 105241

and my formula is:
JulianToDate (tonumber({Query1.Start Date}))

 

[HowardHammerman]

Do you know the start date that the AS400 is using?

If so, forget about the Juliantodate function and create your own logic. Let's assume that the number provided is the number of days since 12/31/1978.

If so, create the following formula:

date(1978,12,31)+ VAL({table.field}[2 to 6])

You may have to play with this using known dates to get the right start date.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[giggles7840]

I found out that the format of this field is CYYJJJ. Does anyone have some code for this conversion?

 

[giggles7840]

found it.

StringVar JDEDate;
NumberVar JDECentury;
NumberVar JDEYear;

JDEDate := ToText({table.field}, "000000");
JDECentury := ToNumber(JDEDate[1]);
JDEYear := ToNumber(JDEDate[2 to 3]) + 1900 + (100 * JDECentury);

(Date (JDEYear,01,01) + (ToNumber(JDEDate[4 to 6]) - 1));

Only I had to remove the "000000" and the -1

 

[giggles7840]

ok so now my problem with this formula is that it doent take into account 0's in the original field. I thought I could fix it by writing it like this:

if {Query1.START DATE} = "0" then "0" else
if {Query1.START DATE} <> "0" then
stringVar JDEDate;
NumberVar JDECentury;
NumberVar JDEYear;
JDEDate := {Query1.START DATE};
JDECentury := ToNumber(JDEDate[1]);
JDEYear := Tonumber(JDEDate[2 to 3]) + 1900 + (100 * JDECentury);
(Date (JDEYear,01,01) + (ToNumber(JDEDate[4 to 6])- 1 ));

However this still throw a message that says "the string is non-numeric" and it highlights "Tonumber(JDEDate[2 to 3])"

Can anyone help me fix this?

 

[lbass]

Try:

stringVar JDEDate;
NumberVar JDECentury;
NumberVar JDEYear;
if {Query1.START DATE} = "0" then date(0,0,0) else
if {Query1.START DATE} <> "0" then
(
JDEDate := {Query1.START DATE};
JDECentury := ToNumber(JDEDate[1]);
JDEYear := Tonumber(JDEDate[2 to 3]) + 1900 + (100 * JDECentury)
);
Date (JDEYear,01,01) + ToNumber(JDEDate[4 to 6])- 1
;

-LB

 

[HowardHammerman]

It is not that complicated.

Since the format is CYYJJJ
and example of a field is 105241
we can assme that the first digit indicates the century and that the number 1 indicates the current century. Presumably the number 0 would indicate the prior century.
The next two digits is the year the final three is the day of the year.

I would use the following code:

WhilePrintingRecords;
stringvar jtext := totext({table.field},0,"");
numbervar yr :=IIF(jtext[1]="1",2000,1900)+VAL(jtext[2to3]);
date(yr-1,12,31)+VAL(jtext[4 to 6])

The date you provided, 105241 becomes August 29, 2005.

I hope this helps.

Howard Hammerman,
Crystal Training and Crystal Material
On-site classes and one-on-one coaching
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269