Error messages when sending date-time stamp to Oracle Database

[DanNatCorning]

  I have a complex application that communicates with an Oracle database.  One of the table values that gets written to the table is a date-time stamp.  Until recently, the user only required a date, and this was never an error. (The format String used was %d-%b-%Y)  Now they need a time-stamp as well, so I attempted to edit the format string input of the format date/time string function to include the time. I have tried numerous format strings, each of which returns an error on execution.  Depending on which format I use, I get one of 3 different error messages.  The really odd thing is that both the date and time always get stored in Oracle without any problem (regardless of what date-time I use, as long as I include the date and any part of a time stamp - I get the entire timestamp even if I only include %I for the hour).  The only issue is that LabVIEW generates an ODBC error if I try to include the time to the date-time stamp.  In other words, teh date/time stamp writes to the Oracle database, but an ODBC error occurs every time.  I've included a word doc with screen captures of the format strings used, the resultant date/time stamps written to Oracle and the error messages generated within LabVIEW. Any advice would be most appreciated.

[mikeporter]

What does the actual insert statement look like?

Mike...

[Andrew_E]

Hello,

Do you have the Database Connectivity Toolset? If you do, try using the DB Tools Format Datetime String VI from the Utilities sub-pallete to correctly format the date/time entry that you need to send to the Oracle database. 

[DanNatCorning]

Andrew, I do have the db connectivity kit, and I tried your suggestion.  THe error message I get now is:

<ERR>Exception occured in Microsoft OLE DB Provider for ODBC Drivers, [Oracle][ODBC][Ora]ORA-01858: a non-numeric character was found where a numeric was expected
.  in Conn Execute.vi

I am at a complete loss.  The strangest thing is that no matter what I try the date-time that gets to Oracle is fine.  I just keep getting this error message.

[Zinnium]

Dan,

I ran into this problem already and its actually the formatting in the database that is the real problem. 

Change the date/time field's formatting to general (or blank) and see if that works. Also, run the maintenance on it before you run your app.

If this doesn't do it:  Take the data out of the field and save it in another table.  Delete the date feild and recreate it as a date & time field but leave its formatting as "blank". By leaving alone; it will allow the database to take the info in the formate and apply it any which way it can.   Now, add all the old data back into the field, and try your program. 

[mikeporter]

With oracle, time data has to be sent to the database in particular way. What does the insert statement in you code look liike. That is where you will find the problem.

Mike...

[Nils_Gokemeijer]

Oracle has a standard date format: 12-Mar-2009 that it accepts as a string, but if you want more than that, you need to use the todate() function. I used this string going into the standard LabVIEW ''Format date/time sting vi' 

to_date('%d-%b-%Y %H:%M:%S', 'DD-MON-YYYY HH24:MI:SS') to create the value needed for Oracle.

Then I use standard format string to create SQL: insert into mytable (mydate) values(%s) and select * from my table where mydate = %s using the string above for %s.

I think you can also define a global format for all time strings but I haven't bothered figuring that out.