HELP!!!Date field Conversion :(

[cancerjenn]

Well, here is my issue. I'm trying to export the date field from ONEWORLD Xe into crystal report 8.5. However, the date format that crystal report 8.5 uses is GREGORIAN calendar and OneWorld Xe uses the Julian calendar. So when I extract the date field from ONEWORLD Xe into crystal report 8.5 and using a function in CRYSTAL report8.5 called JuliantoDate
to convert the date into Gregorian, it gives me some werid numbers due to the decimal in the date field. So I was wondering if there is a way to truncate the decimals in the date field or convert it into a string before i use the JULIANTODATE function?

 

[dgillz]

There are several ways to do this, however I need to see your data. Please paste an example or two of your data and I am sure I can help. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[cancerjenn]

Well, here is some example of the data !

OneWorld Xe CR8.5 (should convert it as yr/month/date)

098185 --------> 1998/7/4
102038 --------> 2002/2/7

And when I extract the date field into CR8.5, it converts 102038 into 102,038.00 so how can I get rid of the decimals

 

[dgillz]

I believe 102,038.00 is just the default format of any numeric field. You can change this by rt click, format field, and choose no decimals, no thousands seperator. However that will not solve your problem.

In your example above the last 3 digits mean the day of the year - July 4th of '98 is the 185th day of the year, whilke the first 3 are the year itself, if you add 1900 to it.

Use 2 formulas, your first formula, to find the first day of the year:
@firstofyear
Date(truncate({yourfield}/1000)+1900,1,1)

your second formula, to get the proper date as expressed in your post:
@date
Dateadd("d",val(right(totext({yourfield},0,"&quot,3))-1,@firstofyear)

Let me know if you any help with this.
Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[dgillz]

Oh yeah, I forgot, to get to the Year-month-day format, just rt click format field, customize, go to the date tab and select diplay order. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[cancerjenn]

Hi Dgillz,

It's working! thank you so much!Do you know where i can learn more about Crystal Rpeort formulas? I have couple website plus some books but this problem wasnt addressed. Again, thanks

 

[dgillz]

I am not suprised this was not addressed. In fact this is the only time I have ever seen a date in that format.

The best book money can buy, IMO, is George Peck's "Crystal Reports V8.5 - The Complete Reference. By it online for about $40. It comes with a CD with sample reports, formula examples, etc.

In term of training, report writing and support, I offer these services, please email me for details. Software Support for Macola, Crystal Reports and Goldmine
dgillz@juno.com

 

[kenhamady]

Peck is a great book (about $30 on bookpool.com), but nothing covers everything. Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Guide to using Crystal in VB

http://www.kenhamady.com/

tek@kenhamady.com