Re: Date conversions in PL/SQL

Laurent,

We had a similar requirement, and came up with the following package. Hope it helps ...

CREATE OR REPLACE PACKAGE PKG_UnixDate AS  FUNCTION To_DateU (i_date IN DATE) RETURN PLS_INTEGER ;   PRAGMA RESTRICT_REFERENCES (To_DateU, WNDS, RNDS) ;  FUNCTION From_DateU (i_Udate IN PLS_INTEGER) RETURN DATE ;   PRAGMA RESTRICT_REFERENCES (From_DateU, WNDS, RNDS) ; END pkg_UnixDate ;
/
CREATE OR REPLACE PACKAGE BODY pkg_UnixDate AS

    k_BaseDate CONSTANT DATE := to_date('01011970','DDMMYYYY');  k_SecsPerDay CONSTANT PLS_INTEGER := 86400;  k_BaseDate_J CONSTANT PLS_INTEGER := to_char(k_BaseDate, 'J'); --
FUNCTION To_DateU (i_date IN DATE) RETURN PLS_INTEGER IS BEGIN
 RETURN ((to_char(i_date,'J') - k_BaseDate_J) * k_SecsPerDay) + to_char(i_date,'SSSSS');
END ;
--
FUNCTION From_DateU (i_Udate IN PLS_INTEGER) RETURN DATE IS BEGIN
 RETURN k_BaseDate + (i_Udate / k_SecsPerDay); END ;
END PKG_UnixDate ;
/

laurent dufosse <laurent.dufosse_at_free.fr> wrote in message news:YhWD3.178$vD1.743604_at_nnrp1.proxad.net...
> I have a big problem to convert date in julian unix format (number of
> seconds from 1/1/1970)
> in a pl/sql package.
> Oracle don't understand julian at the same way in Unix.
>
> SO, is there every body have integrate an L3G-conversion in an Oracle 8i
> database (On HP-UX) ?
>
> thanks a lot for responses.
>
> laurent Dufosse
>
>
Received on Wed Sep 15 1999 - 19:17:06 CDT