Re: pro*c and date coulumns

From: Thomas Kyte <>
Date: Fri, 28 May 1999 12:35:14 GMT
Message-ID: <>

A copy of this was sent to (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?
>Sent via
>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()
    time_t t;

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


    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 for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
Oracle Service Industries
Reston, VA USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation
Received on Fri May 28 1999 - 14:35:14 CEST

Original text of this message