JDE Date Format in Cognos

pgeriesci

Member
I am trying to develop a Cognos report that includes dates from JD Edwards. I know that JDE stores the date as CYYDDD. The problem is translating this date to a Cognos report as mm/dd/yyyy. Has anyone had any luck in this translation and if so how did you accomplish it?
JDE OneWorld XE
SP 16.1
NT 4.0
SQL 7.0

JDE OneWorld Xe
SP-16.1
NT 4.0
SQL 7.0
 
Hi There,

This is a common issue for anybody using 3rd party BI Tools. I have seen a few different solutions for Cognos Impromptu. One of them is the following formula:

If (Date – For G/L (and Voucher) <> 0 and Date-For G/L (and Voucher) <= 99999) then (datetime-to-date(days-from-1900-to-datetime(date-to-days-from-1900(datetime-to-date(make-datetime(string-to-integer((‘19’ + substring(number-to-string(Date-For G/L (and Voucher)),1,2))),01,01)))+string-to-integer(substring(number-to-string(Date-For G/L (and Voucher)),3,3))-1))) else if (Date-For G/L (and Voucher)>99999)then (datetime-to-date(days-from-1900-to-datetime(date-to-days-from-1900(datetime-to-date(make-datetime(string-to-integer((‘20’ + substring(number-to-string(Date-For G/L (and Voucher)),2,2))),01,01)))+string-to-integer(substring(number-to-string(Date-For G/L (and Voucher)),4,3))-1)))else NULL


Please Note: this will convert CYYDDD into dd/mm/yyyy and not into mm/dd/yyyy as you require, but I hope it will give you enough info nevertheless.

I have seen a shorter solution elsewhere, but do not have this info with me at the moment. Should you require an alternative I'll gladly provide it, but I expect you'll get a few more suggestions from other users.

Rgds, Sef

Sef van den Nieuwelaar
Australia
B732 on NT, XE on NT, B732/A73 on AS400, B733 on NT
 
At our site we have created 2 tables
F55365 - Holds todays date in Julian and in format 'YYYYMMDD', just one record with additional two blank columns. 1 numeric and 1 alphanumeric. The additional columns are used to join this table to any user queries.
(A scheduled program updates this with new date every night)

F55365D
Holds a list of dates starting from 1981 and ending year 2050
One column with julian format, another with 'YYYYMMDD'

This approach allows us to create user queries on AS/400 with readable dates as well as allowing us to use MS Access or any other 3d party tool without having to write procedures to convert the dates.



Aarto Reponen
Application Consultant
SYSteam Applications
E-mail: [email protected]
http://www.systeam.se
 
You can create UDF in Cognos and write a simply dll (in c or vb) to convert
the date. I saw an article on the cognos site for this

Simon
 
There are functions available for Impromptu that will convert the julian date to an actual date. These can be added to the IMPFUNCT.INI file for permanent use. The code converts the JDE Date to a date time which can then be converted to an actual date using the datetime-to-date conversion.
The code can be obtained from the cognos web site.


Neil Mackenzie
I T Manager
Smiths Manufacturing (Pty) Ltd
Tel: (Direct) +27 (0) 31 7194142
Cell: +27 (0) 82 773 9605
Fax +27 (0) 31 7194444
e-Mail: [email protected]

You can create UDF in Cognos and write a simply dll (in c or vb) to convert
the date. I saw an article on the cognos site for this

Simon




--------------------------
 
I have seen a lot of codes being published in the past two weeks with the
Date Format issues and I have noticed that some of them are not fool proof.
Therefore, I have decided added my date conversion SQL for 1900-2099. I
would like to point out that JDE does not store date in
 
Back
Top