Skip to Main Content
Forums

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

Convert Julian Date JDE to mm-dd-yyyy

OviedityantoSep 26 2018 — edited Sep 27 2018

Dear Experts,

How to convert this julian date (JDEdwards ERP format) "118197" to 07-16-2018 (mm-dd-yyyy) ?

I've try

select to_date(concat(to_char(to_number(substr(118197,1,3)+1900)),substr(118197,4,3)),'YYYYDDD') from dual

And the result is 16-07-2018 (dd-mm-yyyy)

Sincerely,

Oviedityanto
This post has been answered by John Thorton on Sep 26 2018
Jump to Answer

Comments

John Thorton
Answer 
Oviedityanto wrote:
Dear Experts,
How to convert this julian date "118197" to 07-16-2018 (mm-dd-yyyy) ?
I've try
  1. selectto_date(concat(to_char(to_number(substr(118197,1,3)+1900)),substr(118197,4,3)),'YYYYDDD')fromdual
And the result is 16-07-2018 (dd-mm-yyyy)
Sincerely,
Oviedityanto

so close yet so far

BTW there are simpler way to do the same, but I just built off what you posted

do as below

  1* select to_char(to_date(concat(to_char(to_number(substr(118197,1,3)+1900)),substr(118197,4,3)),'YYYYDDD'),'mm-dd-yyyy') from dual

SQL> /

TO_CHAR(TO

----------

07-16-2018

Marked as Answer by Oviedityanto · Sep 27 2020
Oviedityanto

Thanks John Thorton..

mathguy

As John said, there are (much) simpler ways, which you would do well to use instead of the convoluted formula you devised.

For example:

select to_date(to_char(1900000 + 118197), 'yyyyddd') as gregorian_date from dual;
GREGORIAN_DATE
--------------
2018-07-16

The 1900000 you add to the number is to convert from 118 (years after 1900) to the actual year, 2018. 1900000 is fixed for all such computations, it is given externally (such numbers are called "magic" numbers).

Then, if you need a string representation (not a date!) in a specific format, apply TO_CHAR to this result, with the desired format model.

2 people found this helpful
Oviedityanto

Thanks mathguy..

unknown-7404 
How to convert this julian date "118197" to 07-16-2018 (mm-dd-yyyy) ?

Not possible since that value is NOT the julian version of 07-16-2018.

What you have is NOT a julian date

https://en.wikipedia.org/wiki/Julian_day

2 people found this helpful
mathguy

... except that this is an Oracle document:

https://docs.oracle.com/cd/E26228_01/doc.93/e21961/julian_date_conv.htm#WEAWX259

I found it by Googling for "Oracle Julian date", and moving past the documents that seemed to refer to the "standard" definition (which is the one you linked to).

The OP did not invent the concept, the name for it, or the notation. It is in Oracle documentation (of what, I have no clue).

1 - 6
Locked Post
New comments cannot be posted to this locked post.

Post Details

Locked on Oct 25 2018
Added on Sep 26 2018
#query, #sql-pl-sql
6 comments
17,091 views