Home » Open Source » Programming Interfaces » OTL Date time not working with minus (-) sign (Oracle 11g)
OTL Date time not working with minus (-) sign [message #678055] Tue, 05 November 2019 07:12 Go to next message
Messages: 55
Registered: January 2011
Location: india
Hello Friends,

I am not sure if this is the right forum to discuss OTL code related problems.
If not, then please advise in which forum can I post OTL code related queries.

I have a code which does not work if I pass an OTL date to the SQL and if the passed value is compared with another date but by using minus (-) sign.
It works fine if I use = sign.

In the database :-

In the OTL code :-

otl_connect *db1::db1OtlConnect = NULL;

otl_datetime dt1;

dt1.year = 2019;
dt1.month = 11;
dt1.day = 10;

static std::string SQL1 =
  "    FROM TEST1 "
  "    WHERE TRUNC(:dDatetime<timestamp>) - TRUNC(DATE1) > 10 ";  /*****THIRD LINE IN SQL*****/

		if (db1OtlConnect == NULL)
			db1OtlConnect = JBLdbCxnManager::getConnection();  /* JBLdbCxnManager is an internal library of my company and works fine without any issues */

		static otl_stream otlSelectModeStreamA(1, SQL1.c_str(), *db1OtlConnect);
		otlSelectModeStreamA << dt1;	
		while (!otlSelectModeStreamA.eof())
			{/* Process columns A,B, and DATE1*/}
	catch (otl_exception& p)
	/* print p */

This gives me the error :-

ORA-00932: inconsistent datatypes: expected NUMBER got DATE

If 3rd line in SQL is replaced with :-

" TO_CHAR((:dDatetime<timestamp>) - TRUNC(DATE1)) > 10 "; 

then I get the error :-

ORA-00932: inconsistent datatypes: expected CHAR got DATE

If 3rd line in SQL is replaced with :-

"    WHERE TRUNC(:dDatetime<timestamp>) >= TRUNC(DATE1) ";

then it works fine.

Please help/advise.


[Updated on: Tue, 05 November 2019 07:13]

Report message to a moderator

Re: OTL Date time not working with minus (-) sign [message #678059 is a reply to message #678055] Tue, 05 November 2019 08:14 Go to previous message
Michel Cadot
Messages: 67570
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I don't know OTL but here are some thought:

* does not this "otlSelectModeStreamA(1, SQL1.c_str()," mean the parameter is a string? In this case "TRUNC(:dDatetime<timestamp>)" implies an implicit conversion using the default date format.

* does not "otl_datetime" also contain hour, minute and second? what happen if you set all values and not juts year, month and date?

* you have a workaround so you can replace "TRUNC(:dDatetime<timestamp>) - TRUNC(DATE1) > 10" by "TRUNC(:dDatetime<timestamp>) > TRUNC(DATE1) + 10"

Previous Topic: Oracle OleDB driver pads trailing spaces
Next Topic: plsql output to STDOUT
Goto Forum:

Current Time: Fri Dec 04 05:07:59 CST 2020