Convert Julian date to mm/dd/yyyy -

building semantic layer
1

Hi ALL,

We are using JD Edward, where date is stored in julian format in Oralce table. We want to display this date in mm/dd/yyyy format.
We use Oracle’s to_date() function, but the date displayed by JDE application and and one displayed by Oracle is different.

How to create a date object to display the correct date as being displayed by JDE application.

Working Environment:
PeopleSoft Enterprise One
Oracle Version 9

Any idea how to achieve this?

thanks & regards
sunil

Sunil (BOB member since 2002-10-02)

2

Could this be a Locale issue?

shamit (BOB member since 2004-07-01)

3

Hello Sunil, I have used JDE before, The way we did was using stored procedure call for all the date objects. The procedure would convert the julian date and return regular date. But you can create an date object like this: I have used Invoice date from Sales History as an example:

to_date(decode(to_char (F42119.SDIVD), '0', '', to_char(to_date(to_char(substr(to_char(F42119.SDIVD),1,length(to_char(F42119.SDIVD))-3)+1900) 
|| '-' || substr(to_char(F42119.SDIVD),-3), 'YYYY-DDD'), 'mm-dd-yyyy')), 'mm-dd-yyyy')

This works perfectly for me. Hope this helps. :idea:

Jony (BOB member since 2004-07-09)

4

You will find a solution to this by checking Resolution Entry 4840 on the Tech Support site.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

5

Mike,

The JD Edwards Julian dates are different than Oracle Julian dates.

Jony (BOB member since 2004-07-09)

6

Well, that’s just silly! :roll_eyes: :wink:

Well, I’m not familiar with JDE Julian Dates. Can you tell me what they look like. Maybe I can help out with the solution.

MichaelWelter :vatican_city: (BOB member since 2002-08-08)

7

Thank you very much Jony

It is working perfectly…

Yes, the computation of Julian Date in Oracle is different than that of JDE

I believe in JDE the base date for computation of Julian is 01/01/1900.
In MS-SQL the base date is 01/01/1900.
In Oracle the base date is January 10, 4712 BC

Thank you very much everybody

Sunil

Sunil (BOB member since 2002-10-02)

8

Does anyone know how to convert Jony’s function to MS-SQL rather than Oracle?

award (BOB member since 2004-10-08)