dot zero in a end of a datetime

[dpdfr_ag]

Hi,
I explain to you my problem.
I have a tMySQLInput who search a datetime in my database. I have linked her with a main link to a tFlowToIterate.
Then I linked my tFlowToIterate to a tInformixInput.
Inside it I have a query who use the results of my tMySQLInput ( the datetime )

((Date)globalMap.get("row18.myDate"))

When I launch I have this error :

Exception in component tInformixInput_5
java.sql.SQLException: Extra characters at the end of a datetime or interval.
	at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
	at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
	at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762)
	at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574)
	at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490)
	at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656)
	at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629)
	at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
	at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
	at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
	at projet_albin.myProject_0_1.myProject.tMysqlInput_9Process(myProject.java:1487)
	at projet_albin.myProject_0_1.myProject.runJobInTOS(myProject.java:1952)
	at projet_albin.myProject_0_1.myProject.main(myProject.java:1826)
Caused by: java.sql.SQLException
	at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
	at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767)
	... 10 more

So I use a tMsgBox to get the query of tInformixInput and I have a .0 at the end of my datetime... but I don't understand where it came.
Exemple :

2011-08-29 11:25:18.0

In my schema, myDate is in Date format.
Any idea ?
Thanks.

[Anonymous]

Hi
For testing, link it to tLogRow from tMysqlInput and see whether it comes from tMysqlInput.
Waiting for your feedback!
Best regards
Shong

[dpdfr_ag]

Hi Shong,
I do it and my datetime is normal, without .0 in the tLogRow

|2011-08-25 18:58:43|
|2011-08-25 19:06:31|

[dpdfr_ag]

For information.
I have look into my others Job if I have the same problem.
I see that this problem appears only when we reuse the datetime like a String....
I explain, if I write my datetime in a tfileOutput or a tLogRow, no problem I have my correct datetime, but if I use it in a tJavaRow or in a other query ( for exemple : ((Date)globalMap.get("row18.myDate")) ), the problem appears.
Exemple of result with a tJavaRow with only a System.out.println(input_row.myDate);

 connected
2011-08-25 18:58:43.0
2011-08-25 19:06:31.0
2011-08-25 19:06:39.0
2011-08-25 19:06:59.0
2011-08-25 19:08:04.0
2011-08-25 19:08:16.0

Strange...
Perhaps this may help you.

[alevy]

You need to manually convert the Date to a String in the correct format for your SQL statement e.g. ... WHERE testdate = "+TalendDate.formatDate("yyyy-MM-dd HH:mm:ss",(Date)globalMap.get("row18.myDate")).
(Talend does this for you when you use tfileOutput or tLogRow, applying the Date Pattern from the schema.)

[_AnonymousUser]

Hi,
I'm posting here because I'm facing a problem similar to this one.
I build all my SQL queries in at tJava component. This is what my select statement looks like -
qryString.append("SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, ");
qryString.append(" cp.conprmid_ref, ");
qryString.append(" (CASE WHEN c.mildates_ustmntdt > '").append(context.convdate)).append("' ");
qryString.append(" THEN ");
qryString.append(" DATE(ADD_MONTHS(c.mildates_ustmntdt, -12)) ");
qryString.append(" ELSE ");
qryString.append(" c.mildates_ustmntdt ");
qryString.append(" END) anniv_date ");
context.convdate contains a valide dd/MM/yyyy format date. When I print this query on screen before a tInformixInput component, this is what I get (formatted for your viewing pleasure)-
SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, cp.conprmid_ref,
(CASE WHEN c.mildates_ustmntdt > '2012-04-23'
THEN DATE(ADD_MONTHS(c.mildates_ustmntdt, -12))
ELSE c.mildates_ustmntdt
END) anniv_date

When I execute this query in the Informix query editor(dbaccess), it works. However, when I run this in a tInformixInput component I get the following stack trace -
Exception in component tInformixInput_2
java.sql.SQLException: String to date conversion error
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixInput_2Process(cntr_prem_base.java:3378)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tJava_3Process(cntr_prem_base.java:2716)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixConnection_1Process(cntr_prem_base.java:2552)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tRunJob_1Process(cntr_prem_base.java:1443)
at aquila.cntr_prem_base_0_1.cntr_prem_base.runJobInTOS(cntr_prem_base.java:5123)
at aquila.cntr_prem_base_0_1.cntr_prem_base.main(cntr_prem_base.java:4774)
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767)
... 13 more

However, when i reformat my date as yyyy-MM-dd, it executes properly. My DBDATE is DMY4
Any idea what's going on here?

[_AnonymousUser]

Sorry for duplicating this. Reposting with bbcode.
I'm posting here because I'm facing a problem similar to this one.
I use talend 4.1.1.r50363.
I build all my SQL queries in at tJava component. This is what my select statement looks like -

Query:
qryString.append("SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, ");
qryString.append(" cp.conprmid_ref, ");
qryString.append(" (CASE WHEN c.mildates_ustmntdt > '").append(context.convdate)).append("' ");
qryString.append(" THEN ");
qryString.append(" DATE(ADD_MONTHS(c.mildates_ustmntdt, -12)) ");
qryString.append(" ELSE ");
qryString.append(" c.mildates_ustmntdt ");
qryString.append(" END) anniv_date ");

context.convdate contains a valide dd/MM/yyyy format date. When I print this query on screen before a tInformixInput component, this is what I get (formatted for your viewing pleasure)-

Query:
SELECT c.cntrctid_ref, c.cntrctid_number, c.mildates_strtdt, cp.conprmid_ref,
(CASE WHEN c.mildates_ustmntdt > '2012-04-23'
THEN DATE(ADD_MONTHS(c.mildates_ustmntdt, -12))
ELSE c.mildates_ustmntdt
END) anniv_date

When I execute this query in the Informix query editor(dbaccess), it works. However, when I run this in a tInformixInput component I get the following stack trace -

Stacktrace :
Exception in component tInformixInput_2
java.sql.SQLException: String to date conversion error
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.a(IfxSqli.java:3453)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3762)
at com.informix.jdbc.IfxSqli.dispatchMsg(IfxSqli.java:2574)
at com.informix.jdbc.IfxSqli.receiveMessage(IfxSqli.java:2490)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1656)
at com.informix.jdbc.IfxSqli.executeStatementQuery(IfxSqli.java:1629)
at com.informix.jdbc.IfxResultSet.a(IfxResultSet.java:206)
at com.informix.jdbc.IfxStatement.executeQueryImpl(IfxStatement.java:1228)
at com.informix.jdbc.IfxStatement.executeQuery(IfxStatement.java:218)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixInput_2Process(cntr_prem_base.java:3378)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tJava_3Process(cntr_prem_base.java:2716)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tInformixConnection_1Process(cntr_prem_base.java:2552)
at aquila.cntr_prem_base_0_1.cntr_prem_base.tRunJob_1Process(cntr_prem_base.java:1443)
at aquila.cntr_prem_base_0_1.cntr_prem_base.runJobInTOS(cntr_prem_base.java:5123)
at aquila.cntr_prem_base_0_1.cntr_prem_base.main(cntr_prem_base.java:4774)
Caused by: java.sql.SQLException
at com.informix.util.IfxErrMsg.getSQLException(IfxErrMsg.java:407)
at com.informix.jdbc.IfxSqli.E(IfxSqli.java:3767)
... 13 more

However, when i reformat my date as yyyy-MM-dd, it executes properly. My DBDATE is DMY4
Any idea what's going wrong here?

[alevy]

If you append a Date to your SELECT String, you have no control of the format used for that Date. As I said above, you need to manually convert the Date to a String in the correct format for your SQL statement e.g.

qryString.append("      (CASE WHEN c.mildates_ustmntdt > '").append(TalendDate.formatDate("dd/MM/yyyy",context.convdate)).append("' ");

And you don't need to re-post, you can just edit your previous post if you register.