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
sinpeak
Messages: 59
Registered: January 2011
Location: india
Member
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 :-
CREATE TABLE TEST1(A NUMBER(9),B VARCHAR2(20), DATE1 DATE);

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 =
  "  SELECT A,B,DATE1 "
  "    FROM TEST1 "
  "    WHERE TRUNC(:dDatetime<timestamp>) - TRUNC(DATE1) > 10 ";  /*****THIRD LINE IN SQL*****/

	TRYBLOCK
	{
		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.

Thanks.

[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: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
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 12.1.0.2.0 OleDB driver pads trailing spaces
Next Topic: plsql output to STDOUT
Goto Forum:
  


Current Time: Mon Dec 30 11:04:41 CST 2024