How To Get A Date… Out Of E1

No this is not a dating service!

Today we will cover the proprietary date format that is used in most of the files in E1.

Well, let’s just dive in…

Today’s date is 4-5-2007. In E1-land that is 107095.

Here’s the breakdown:

  • E1 Date: 107095
    • Year: 1900 + 107 = 2007
    • Date: 095
      • The 95th day of the year (2007) = April 5th
    • Complete Date: April 5th 2007

Hey! What about the time?

  • E1 Time: 90154
    • 9:01:54 AM
  • E1 Time: 210154
    • 21:01:54 = 9:01:54 PM

Here is a page I put together that uses javascript to retrieve the dates.

Technorati Tags: e1,enterpriseone,erp,peoplesoft,oracle,jde,jd edwards

2 thoughts on “How To Get A Date… Out Of E1

  1. Thank you for your post on getting dates; however I’m kind of stranded when trying to convert this number (95832) field to time in (‘HH24:MI:SS’) format such that i get 9:58:32 as output instead of 09:58:32.
    My query was SELECT TO_CHAR(TO_DATE(JCSBMTIME,’HH24:MI:SS’),’HH24:MI:SS’) AS Time FROM SVM900E1.F986110 where JCJOBNBR = ‘48444’, but its throwing the error ORA-01850 hour must be between 0 and 23.

    Help me solve it.

    1. Hey, Elizabeth! Glad this site is helping you out. I think what you are looking for is something like this:

      to_char(to_timestamp(to_char(JCSBMTIME,'000000'),'HH24:MI:SS'),'HH24:MI:SS')

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Previous post EnterpriseOne (E1) OMW Cleanup
Next post How To Get A Date… Out Of E1 Part 2