Re: pro*c and date coulumns

A copy of this was sent to smklad_at_my-deja.com (if that email address didn't require changing) On Thu, 27 May 1999 21:56:06 GMT, you wrote:

>How do you declare date fields in pro*c.
>I tried char dt_p[64] or Varchar dt_p[64]
> Does anyone have a snippet for handling dates
>and times with pro*c, also if you declare a PL/SQL
>variable of type DATE how do you copy that to a
>'text' date/time values?
>
>TIA,
>STEVEN
>
>
>Sent via Deja.com http://www.deja.com/
>Share what you know. Learn what you don't.

Unless you want to deal with the 7byte internal date format used by Oracle internally (highly *not* recommended) you will fetch dates into pro*c programs by converting them into strings typically. for example:

exec sql begin declare section;
varchar my_date[40];
exec sql end declare section;

   exec sql select to_char(sysdate,'dd-mon-yyyy hh24:mi:ss' )

              into :my_date
              from dual;

If you want the date in a format that is a C time type and you are sure it'll fit (eg: its after 1/1/1970 and before some time in the future when the C time type stops working) AND you know your timezone (you can use a C function to get that) you can use:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    time_t t;
EXEC SQL END DECLARE SECTION;     EXEC SQL SELECT (new_time(sysdate,'EDT','GMT') -

                     to_date('01-jan-1970','dd-mon-yyyy')) * (24*60*60)
               INTO :t
               FROM DUAL;

    printf( "C Time = %d\n", time(NULL) );     printf( "SQL Time = %d\n", t );
}

To go back in, you can:

...
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;

the new_time function and its inputs are what you would use in an update or insert statement.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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