Re: how to insert datetime in PRO*C

kortikar_at_mipos2.intel.com (Aniruddha Kortikar) writes:

>varchar var1;
>varchar var2;
>varchar datetime[15];
>datetime.arr contains "mm/dd/yy hh24:mi" i.e "12/30/92 15:54"
>datetime.len contains 14
 

>char stmt[1000];
>strcat(stmt,"insert into tablename (f1,f2,date_field) values (:v1,:v2,:v3)");
>exec sql prepare s1 from :stmt
>exec sql execute s1 using :var1,:var2,:datetime
 

>ORA-01859 : a number was found in a date and a letter was expected.
 

>How can I force oracle to consider this as datetime. in normal sql I would have
>done
>insert into tab (f1,f2,date_field) values
> (v1,v2,to_date('char text','mm/dd/yy hh24:mi');
 

>exec sql execute s1 using :var1,:var2,to_date(....) does not work

to_date() is SQL-code. It is not a host variable. Therefore it has to be in the sql-statement to be executed, not in the host variable list. Use something like this:

strcat(stmt,"insert into tablename (f1,f2,date_field) "); strcat(stmt,"values (:v1,:v2,to_date(:v3, 'mm/dd/yy hh24:mi'))"); exec sql prepare s1 from :stmt
exec sql execute s1 using :var1,:var2,:datetime

>Aniruddha Kortikar (kortikar_at_mipos2.intel.com)
>-------------------------------------------------------------------------------
>E-mail : kortikar_at_mipos2.intel.com | A billion here, a billion there ...
>Phone(W): 408 765 5515 | pretty soon it adds upto real money.
>-------------------------------------------------------------------------------
 Olli Mikkonen

-- 
Olli Mikkonen		: Any opinions above are purely my personal ones.
Internet: 		: Especially they do not reflect my employers
omikko_at_cs.joensuu.fi	: official opinions.