Re: Pro*C question: DATE datatype?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 16 May 1998 14:50:30 GMT
Message-ID: <3561a6d1.3819331_at_192.86.155.100>


A copy of this was sent to kirill_at_cs.ualberta.ca (Kirill Richine) (if that email address didn't require changing) On 15 May 1998 23:23:40 GMT, you wrote:

>Hi!
>
>I was looking through the Pro*C programmer's guide for any hint on how
>to convert DATE Oracle datatype into something that C supports.
>
>Apparently, proc itself does not recognize DATE.
>
>Is there any standard way to handle this datatype, preferably not
>having to use to_char () in the SQL.
>
>Thanks.
>k&

C doesn't really have a date data type. It has various C libraries that do date stuff but thats it.

The dates in Oracle are not stored with a timezone. Most of the C routines only deal with dates in GMT since Jan 1 1970. It you want a C time type AND the date fits the criteria for a C time_t (eg: its > 1/1/1970) AND you know the timezone the date in the database represents the following will do it:

static void process()
{
EXEC SQL BEGIN DECLARE SECTION;
    int 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 );
}

C Time = 892430675
SQL Time = 892430675

You just convert SYSDATE into GMT, subtract January 1'st 1970 from it and multiply by the number of seconds in a day...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA  

http://govt.us.oracle.com/ -- downloadable utilities  



Opinions are mine and do not necessarily reflect those of Oracle Corporation  

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat May 16 1998 - 16:50:30 CEST

Original text of this message