Julian date conversion

[Jeff Y. Y.]

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

[Richard Kuhler]

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

[Alex Filonov]

yuan...@yahoo.com (Jeff Y. Y.) wrote in message news:<a2bc9497.02041...@posting.google.com>...

I don't think it's Julian date. Not in Oracle definition, at least:

1* select to_char(to_date('101032', 'J'), 'MM/DD/SYYYY') FROM DUAL
SQL> /

TO_CHAR(TO_
-----------
08/11/-4436

You see, if you interpret it as Julian, it's way B.C. date.

[Scott Mattes]

Could it be that YYDDD type of julian? Try

to_char( to_date( strt, 'RRDDD' ), 'yyyy mm dd' )

to see.

"Jeff Y. Y." <yuan...@yahoo.com> wrote in message
news:a2bc9497.02041...@posting.google.com...