Re: Date conversions in PL/SQL

Thomas, how do the following with pl/sql function.

Thanks a lot
Ted Chyn(tedchyn_at_yahoo.com)

time_t theTime;
> varchar otime[50];
>
> time(&theTime);
>
> printf( "%s", ctime( &theTime ) );
>
> EXEC SQL SELECT to_char(
> new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
:theTime,
> 'GMT', 'EDT' ), 'dd-mon-yyyy hh24:mi:ss' )
> into :otime from dual;
> ...
>
> Wed May 13 21:35:06 1998
> 13-may-1998 21:35:06
>
> this will do it in the timezone EDT....
>
> All you need to do is ADD the unix time to the date 01-01-1970 and
adjust for
> GMT.
>
> Instead of using new_time to do the timezone conversion -- you can
just add
> "N/24" where N is your offset from GMT
>

In article <uNrgN7t2sqmawYOei+bT6RWDS2pR_at_4ax.com>,   tkyte_at_us.oracle.com wrote:
> A copy of this was sent to "laurent dufosse" <laurent.dufosse_at_free.fr>
> (if that email address didn't require changing)
> On Wed, 15 Sep 1999 23:51:20 GMT, you wrote:
>
> >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
> >
>
> The Unix time format you are talking about above is *not* a julian
date at all.
>
> The JD counts days within one Julian Period of exactly 7980 Julian
years of
> 365.25 days. Start of the JD count is 12 noon 1 JAN -4712, Julian
proleptic
> calendar.
>
> that aside, if you would like to convert the Unix 'seconds since 1970'
number
> into a meaningful date, it is easy to do if you know your timezone:
>
> ...
> time_t theTime;
> varchar otime[50];
>
> time(&theTime);
>
> printf( "%s", ctime( &theTime ) );
>
> EXEC SQL SELECT to_char(
> new_time( to_date('01011970','ddmmyyyy') + 1/24/60/60 *
:theTime,
> 'GMT', 'EDT' ), 'dd-mon-yyyy hh24:mi:ss' )
> into :otime from dual;
> ...
>
> Wed May 13 21:35:06 1998
> 13-may-1998 21:35:06
>
> this will do it in the timezone EDT....
>
> All you need to do is ADD the unix time to the date 01-01-1970 and
adjust for
> GMT.
>
> Instead of using new_time to do the timezone conversion -- you can
just add
> "N/24" where N is your offset from GMT
>
> --
> See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated June
21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Oct 05 1999 - 16:33:52 CDT