Search code examples
oracledatejulian-date

Convert 5 digit date in oracle

I have an Oracle database table with 5 digit Julian dates that I need to convert to date time format.

Sample data

Source  Actual date
40786 ->    2015-09-01 |
40785 ->    2015-08-31 |

First I tried the following 

SELECT to_char(to_date(to_char(40786), 'J'),'DD-MM-YYYY'),
       to_char(to_date(to_char(40785), 'J'),'DD-MM-YYYY')
  FROM dual; 

40786 ->    4601-09-01 |
40785 ->    4601-08-31 |

Since it is wrong I calculated the difference in days (2416481) and formulated the following query

SELECT to_char(to_date(to_char(40786 + 2416481  ), 'J'),'DD-MM-YYYY'),
       to_char(to_date(to_char(40785 + 2416481), 'J'),'DD-MM-YYYY')  
  FROM dual; 

40786 ->    2015-09-01 |
40785 ->    2015-08-31 |

It is correct for above two days but the table has a history since 2010. Will the above adjustment hold correct for the full history. i.e. weekends, leap years etc ...

Many thanks. V

Solution

  • Your problem is that the column is not stored in Julian date. So you can't ask if the conversion will work or not. It seems that the dates are based on 1.1.1904 (= day zero)

    So the conversion is as follows:

     select to_date('1904-01-01','yyyy-mm-dd') + 40786 as dt from dual;

 DT
 ---------- 
 01.09.2015

 select to_date('1904-01-01','yyyy-mm-dd') + 40785 as dt from dual;

 DT
 ---------- 
 31.08.2015

    If it will realy work, can answer only the code in your GUI conversion routine.

    And yes, if you trust in rational software development, you could expect it will work (for dates say in +/- 100 years range).