4

Below is a sample set of data that is supposed to be dates. I am not sure what kind of format it should be but I was told they are julian dates.

     DATE
    92017
    92320
    99002
    99003
    112010
    112011
    112012
    112013

Can anyone convert them into oracle dates? I tried 

select to_date(DATE,'J') from dual

but some results were in the 1950s and 1940s which doesn't seen right for the data we are dealing with. Am i doing it right here?

I also tried this formula from this link: http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd

SELECT 
TO_DATE(1900+(DATE/1000),1,1)+
TO_NUMBER(SUBSTR(TO_CHAR(DATE),4))-1  FROM DUAL;

Thanks

rene
  • 41,474
  • 78
  • 114
  • 152
JohnD
  • 353
  • 4
  • 13
  • 26
  • According to the definition of Julian that I just looked up, this is the number of days since January 1, 4713 BC. A quick calc of 92017/365 gives us about 252 years, which doesn't even get is into AD's yet. Use those numbers in a Julian formula and thats what you get. Perhaps you need to add another number to them first. – Nick.Mc Jul 10 '14 at 02:23
  • Im my experience, people sometimes copy this stuff from excel. Those numbers in excel translate to: 12/6/2151 10/4/2152 1/20/2171 1/21/2171 9/2/2206 9/3/2206 9/4/2206 9/5/2206 - Do these look correct for the data you are dealing with? – Bob Jul 10 '14 at 02:24
  • @Bob Thanks for the input. No, these dates should be within 10-20 years back and forth at most. – JohnD Jul 10 '14 at 02:38
  • @electricllama I saw this guy uses this formula date(1900+(DATE/1000),1,1)+val(right(str(DATE),3))-1 which roughly translate into SELECT TO_DATE(1900+(DATE/1000),1,1)+TO_NUMBER(SUBSTR(TO_CHAR(DATE),4))-1 FROM DUAL; I couldn't get it to work. Here's the http://www.kirix.com/stratablog/jd-edwards-date-conversions-cyyddd – JohnD Jul 10 '14 at 02:41
  • 1
    I'm guessing they would have to be the number of days after a specific starting date. I think you should go back to the person that told you they were Julian with the definition of what Julian means and ask him to try again. – Bob Jul 10 '14 at 03:21
  • 1
    @JohnD. I added your comment to the original question. Can you please expand on "didn't work" in your original question? The official definition and the Oracle definition of julian is "since January 1, 4713". The definition in that link I think is "since 1 Jan 1900". Again if we use 92017, we get year 2152 which again appears incorrect. You need to find out your sources definition of Julian start date, and you need to confirm the numbers you have. This is not a technical issue, it's a communications issue. – Nick.Mc Jul 10 '14 at 03:23
  • @JohnD Have you tried converting it (the numbers) as epoch? Wich is the number of seconds from january 1st 1970 ? – Jorge Campos Jul 10 '14 at 03:36

4 Answers4

5

-- Using JD Edwards Date Conversions if DATE is a number

select to_date(to_char(1900 + floor(DATE / 1000)),'YYYY') + mod(DATE,1000) - 1 from dual;

-- Using JD Edwards Date Conversions if DATE is a string

select to_date(to_char(1900 + floor(to_number(DATE) / 1000)),'YYYY') + mod(to_number(DATE),1000) - 1 from dual;
Bob
  • 1,045
  • 8
  • 10
  • There are two DATE entries in each statement, you need to replace both with the number (or string) you are using. These both work without error on Oracle 10g and Oracle 11g - I can't test them on anything else at the moment. – Bob Jul 10 '14 at 12:59
  • 1
    @bob Although the result after the data conversion does not seem right but it has nothing to do with the script. The script runs. These data are probably corrupted or I am missing something. Thanks – JohnD Jul 10 '14 at 20:36
4

The simplest way that I found to convert from Julian is: -

SELECT TO_CHAR(TO_DATE(your_julian_date,'JSP'),'dd-Mon-yyyy AD') AS DAY FROM dual;

Also, doesn't matter if the date field is number or a String. If a string, simply put the Julian date inside single quotes.

Also, the Oracle definition of Julian is 'number of days since January 1, 4712 BC'. (Many have erroneously commented as 4713 BC). Ref: https://asktom.oracle.com/pls/apex/asktom.search?tag=julian-date

Swastik Raj Ghosh
  • 618
  • 7
  • 15
1

If your date field is varchar:

select TO_DATE(TO_CHAR(to_number('115032')+1900000),'YYYYDDD') jdedate from dual;

If your date fiel is a number:

select TO_DATE(TO_CHAR(115032+1900000),'YYYYDDD') jdedate from dual;

Both Return:

JDEDATE
-----------
01/02/2015
Diego
  • 21
  • 1
  • 3
-1

Simple

select to_date('0101'|| substr(TRN_REF_NO, 8,2), 'DDMMYY') + to_number(substr(TRN_REF_NO, 10,3)) - 1 Trn_dt
Litisqe Kumar
  • 2,512
  • 4
  • 26
  • 40
Jahanzeb Kibria
  • 1