Re: can you set the epoch for julian date in Oracle?

From: Ben <balvey_at_comcast.net>
Date: 15 Feb 2007 05:58:24 -0800
Message-ID: <1171547904.149041.5200@v33g2000cwv.googlegroups.com>

On Feb 15, 8:50 am, "Ben" <bal..._at_comcast.net> wrote:
> I found this message from a couple of years ago and it looks as though
> the original poster uses the same type of Julian date that our ERP
> system uses.
>
> http://tinyurl.com/2w46ee
>
> Our Julian date is just a count of the number of days since 1/1/1900.
> I also read where Oracle's Julian date is a count from some date B.C.
> 4715 or something around there. Is there a way to use the built in
> functions for Julian date if your Julian date is based from days since
> 1/1/1900?

I may have just figured out my own answer,

if
SQL> select to_char(to_date('02/02/2007','MM/DD/YYYY'),'J') from dual;

TO_CHAR

---

2454134

then 2454134 - 107032 = 2347101

so if I add 2347101 to every one of our Julian dates, then that should translate to the same Oracle Julian date.

SQL> select to_char(to_date(107033, 'J'),'MM/DD/YYYY AD') from dual;

TO_CHAR(TO_DA

---

01/15/4419 BC

1 row selected.

SQL> select to_char(to_date(107033+2347101, 'J'),'MM/DD/YYYY AD') from dual;

TO_CHAR(TO_DA

---

02/02/2007 AD

1 row selected.

SQL> select to_char(to_date(107034+2347101, 'J'),'MM/DD/YYYY AD') from dual;

TO_CHAR(TO_DA

---

02/03/2007 AD

1 row selected.

SQL> select to_char(to_date(107046+2347101, 'J'),'MM/DD/YYYY AD') from dual;

TO_CHAR(TO_DA

---

02/15/2007 AD

1 row selected.

Can anyone see any flaw in that? Received on Thu Feb 15 2007 - 07:58:24 CST