DateFormat in JDE

[apap1717]

Hi all

In JDE System, order date shows 2013/02/23 ,but in database(Oracle) ,the data is 112054 ,data type is number(6,0).

How could i convert datatype from number to date?

thanks......

 

[Gomer79]

I am sure there are other posts regarding the JDE date format as well as documentation from Oracle that explain in greater detail but the JDE date format basically works like this:

The first three digits are the year since 1900 and the last 3 are the day of the year. So in your example 112054 is 2012/02/23 which is the 54th day of the year in 2012 which is 112 years after 1900. I am assuming one of your dates is a typo as the years do not match between the dates as 2013/02/23 would be 113054.

Hope this helps.

 

[Ericl]

You asked: How could i convert datatype from number to date?
What conversion are you requesting specifically?

- Conversion in EnterpriseOne business function?

- Conversion in BI Publisher?

- Conversion in SQL?

- Conversion in Prodata=92s DBU?

- Conversion in IBM I Navigator SQL?

- Conversion in Reporting tool like Query/400?

the data is 112054 ,data type is number(6,0).

> How could i convert datatype from number to date?

 

[Gavin Clark]

I user the following in SQL and other query tools to convert JDE julian dates to system format dates:

DATE('20'||(substr(digits(RPDIVJ),2,5)))

Note that JDE sees a julian date in the following format cyyddd (where c = 0 pre 2000 or 1 post 2000) so your date of 2013/02/23 would be seen as 113054, but SQL would see this same julian date as 2013054, so we need to strip the first digit and replace it with '20' to return a date in our system format. So concatonate '20' with the last 5 digits of the date and use the DATE function to come up with a date in the system format. Of course this only works with dates greater than or equal to 2000.

I hope this makes sense, and is of help.

 

[David Robertson]

I used these in Oracle SQL and they worked well. Creating a package/function/stored procedure of some sort can make life easier.
Something similar should work in most other languages.

Date/Time stamp
to_date(TO_CHAR(xxupmj+1900000) || ' ' || lpad(xxTDAY, 6, '0'), 'YYYYDDD HH24MIss') as Date_Time

Date
TO_DATE(TO_CHAR(xxupmj+1900000),'YYYYDDD')

 

[brandonkirsch]

TO_DATE (PSSHPJ + 1900000, 'YYYYDDD') works just fine on Oracle 11g. It's the simplest form I've found for Oracle.

If you have a good DBA, ask them to make two functions, one that goes Date -> JDE Julian and the other for JDE Julian -> Date. Beware of performance issues that UDFs cause on SQL Server.

 

[apap1717]

Thank you for your message， I has got the answer