Date conversion from JDE format 4

[dataprincess]

I'm trying to use Crystal Report Writer to create some reports off of JDE. Problem is that I can seem to convert their dates.

JDE Date = 110365 should convert to 12/31/2010
JDE Date = 100111 should convert to 4/20/2000

The first digit is the century, the next two digits are the last two digits of the year and the three remaining digits are the day of the year. Can anyone help me? This obviously isn't your standard julian date and I'm too close to the trees right now.

 

[kenhamady]

Is this a character string with numbers in it or is it a true numeric value. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[dataprincess]

Numeric

 

[kenhamady]

Date(truncate({your.field} / 1000) + 1900 , 1, 1)

+ Remainder ({your.field},1000)

- 1
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[slimshades26]

This is exactly what I was looking for! Thanks a million times over! Wow.

 

[slimshades26]

What's the formula to convert 20020510 to 102130?

 

[dgillz]

Slimshades,

Go to the crystal decisions website and download the NumbertoDate() function. NumbertoDate() takes an 8 digit integer in teh YYYYMMDD format an converts it to true date. Several software packages, ACCPAC and Macola are the ones I am most familiar with, store the dates in the manner. Software Support for Macola, Crystal Reports and Goldmine
dgilsdorf@mchsi.com

 

[slimshades26]

Thanks. I'll look into that.