Re: Oracle Date Datatype and ODBC

In article <71jtgj$ol7$1_at_phys-ma.sol.co.uk>, Bill Buchan <bbuchan_at_ossian.net> writes
>
>patrick_williams_at_my-dejanews.com wrote in message
><71a1ir$5jv$1_at_nnrp1.dejanews.com>...
>>Can someone give me an example of setting an Oracle Date datatype using an
>>Oracle ODBC driver? I have tried setting the value using the String
>>functionality but I always get an exception when I put the record.
>>
>>Thanks
>>
>>Patrick Williams
>>
>>
>
>
>insert into table (ID,datetimefield) values (1,{ts '1998-11-02 09:13:00'})
>
>ie format of the date field is {ts 'YYYY-MM-DD HH:MI:SS'}. But don't ask me
>what 'ts' stands for (time-string, maybe?)!
>
>- Bill.
>
>
>

Or you could try this :

  SQL = "UPDATE table_name SET column_name = #" & Format(txtDate.Text, "DD-MMM-YYYY") & "#"   Data2.Database.Execute (SQL)

Using the # around the date string explicitly tells the ODBC driver that the field is a date and it does the conversion to the Oracle date type.

I haven't tried it with other format strings, but since this is the default Oracle date format it's no big deal.

This is just using the DAO with the connect property as the connection string.

Hope this help, dates with VB and Oracle are a complete pain !

Cheers,

David

--
David Kemp Received on Tue Nov 03 1998 - 05:23:28 CST