Re: Julian date conversion

From: Richard Kuhler <noone_at_nowhere.com>
Date: Fri, 12 Apr 2002 19:24:46 GMT
Message-ID: <2wGt8.42808$zN.18344873@twister.socal.rr.com>

Well, I doubt it's an Oracle julian since anything in the last few thousand years is in the millions. Actually, those dates you displayed are a little misleading because those are actually BC ...

select to_char(to_date(101032, 'J'), 'DD-MON-YYYY AD') "real" from dual;

real

---

11-AUG-4436 BC If they are in some julian format, then the critical question is when is the epoch of that format. Common values are '01-JAN-1970' or '01-JAN-1000' but those aren't likely given that data either (Oracle's is '01-JAN-4712 BC'). Frankly, there's no hope in guessing, you need to know the actually dates for some of those values.

Richard

"Jeff Y. Y." wrote:
>
> There is a table column in NUMBER(6) type that stores a date in Julian
> format (I believe). I need convert it to a regular date, but can not
> get the right YYYY. Could you please tell me what algorithm I should
> use to get the right result?
>
> Thanks a lot.
>
> ---------------------------------------------------------------
> SQL> select to_char(to_date(strt, 'J'), 'MM-DD-YYYY') "real",
> 2 to_date(strt, 'dd/mm/yy') "wrong",
> 3 strt
> 4 from my_table where rownum < 10;
>
> real wrong STRT
> ---------- --------- ----------
> 08-11-4436 10-OCT-32 101032
> 11-10-4436 10-NOV-23 101123
> 11-10-4436 10-NOV-23 101123
> 11-19-4436 10-NOV-32 101132
> 11-21-4436 10-NOV-34 101134
> 11-21-4436 10-NOV-34 101134
> 12-02-4436 10-NOV-45 101145
> 12-12-4436 10-NOV-55 101155
> 12-19-4436 10-NOV-62 101162
Received on Fri Apr 12 2002 - 14:24:46 CDT