Re: Julian to date conversion

From: Mark D Powell <markp7832_at_my-deja.com>
Date: Fri, 29 Sep 2000 19:21:56 GMT
Message-ID: <8r2q49$bvp$1@nnrp1.deja.com>

In article <BZ2B5.31801$Z2.469920_at_nnrp1.uunet.ca>,   "S Paquin" <stephane.paquin_at_nospam.ispatnet.com> wrote:
> Hi !
> I am trying to convert a julian date to character and it seems that J
> format doesn't allow fractional days. Anybody has a suggestion ?
>
> For example, I try to convert Julian day 2451544.46 to the
> corresponding date/time 31-DEC-1999 15:55:32 in a sql statement.
>
> Any help will be appreciated.
>
> Stephane
>

Observation - Differenct Julian date systems start counting with a different point in time so 2451544 in one Julian system may not be the same date in another Julian system. If you know the start date you can calculate an adjustment factor.

Now to answer your question the idea came to me that you could truncate the input to a whole day that Oracle could convert to an Oracle date and then you could add the remaining fraction of a day. The remaining fraction would be the whole value minus trunc(value) so the sql would look something like: [I ran into a little problem testing and for some reasone just can not see my mistake, but I am off by a couple of hours. I do not know if it has to do with time zones or just something my mind is blocking out]

OPS2> select to_char(sysdate,'J') "Today's Julian" from sys.dual   2 /
2451817

OPS2> select to_Date(trunc(2451544.46),'J') "Date for No" from sys.dual   2 /
31-DEC-99 OPS2> select to_char( to_date(trunc(2451544.46),'J'),'YYYYMMDD HH24:MI:SS')
  2 from sys.dual
  3 /
19991231 00:00:00

OPS2> select to_char((trunc(sysdate) + 0.46),'YYYYMMDD HH24:MI:SS') from sys.dua
l
  2 /
20000929 11:02:24

OPS2> select to_char( to_date(trunc(2451544.46),'J' ) +

  2                  ( 2451544.46 - trunc(2451544.46)  * 1),
  3         'dd-mon-yyyy hh24:MI:ss') "NEW_DATE"

  4 from sys.dual
  5 /
31-dec-1999 11:02:24

I am not sure why my time does not match yours, but I do not have any more time to spend on this so I hope this helps.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Fri Sep 29 2000 - 14:21:56 CDT