which data table has the dates?

[acctg]

Hello,

We are in OneWorld V.sp12.2 release B732. I queried the F03B11 (Accounts receivable invoice detail) table (using MS Query) for a report, but none of the numbers relate to an invoice date. Does anyone know which other table I have to link in order to view the invoice date? Do I have to use some type of formula on the existing numbers to arrive at a date? What does JDE use as the numbering convention for dates?

Thanks for any help.

 

[Sef]

Hi "acctg",

Being from Australia and "having a jump on the rest of the world" (time wise that is), I'll answer this one.

ALL JDE Dates are retained in (pseudo) Julian format. In F03B11 you will find a rather large number of date fields:
DGJ
DIVJ
DICJ
DSVJ
DDJ
DDNJ
RDDJ
RDSJ
LFCJ
SMTJ
VDGJ
URDT
UPMJ
HDGJ

The format of all these dates is CYYDDD, where:
C = Century (0 or to be more precise <missing> = 20th, 1 = 21st)
YY = Calendar Year (99 = '99, 01 = '01)
DDD = Number of date (001 = Jan 01, 259 = Sept 16)
So today's date will be stored as 101259.

JDE uses quite an array of Business Functions to convert dates to and from Julian format, all of which the user does not see. The display of dates depends on user options and operating system settings.
Any reporting through JDE will automatically convert dates. However if you are using a 3rd party reporting tool you will need to convert the dates yourself. (I have actually just sent a reply to a request to convert dates in Cognos).

Good luck,



Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT

 

[clee]

OneWorld stores dates in Julian format. Unless you know the formula, you
should use the OneWorld ODA DSN to access the data which should take care of
date and numeric conversions.

Chi Lee
B7331, SQL 7, NT 4

 

[wsellers]

Although I haven't used MS Query, I have used Crystal reports which has the
same problem with JDE dates. This is the formula I use in Crystal to
convert dates. Although it may not be the best method, it does work to
convert the data. Some additional logic is required to error check blank
date fields.

DateAdd ("y",
ToNumber (Right (ToText ({Datefield}),6))-1,
DateAdd ("yyyy",ToNumber (Left (ToText ({Datefield}),3)),Date(1900,01,01)))



Walt Sellers
Heil Environmental Industries, Ltd.
OW B733.2 SP10, Citrix, Windows NT TS2000, Oracle 8.1.6