
Contributor
2011-08-29
07:10 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
dot zero in a end of a datetime
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 )
When I launch I have this error :
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 :
In my schema, myDate is in Date format.
Any idea ?
Thanks.
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.
411 Views
7 Replies

Anonymous
Not applicable
2011-08-29
10:05 AM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi
For testing, link it to tLogRow from tMysqlInput and see whether it comes from tMysqlInput.
Waiting for your feedback!
Best regards
Shong
For testing, link it to tLogRow from tMysqlInput and see whether it comes from tMysqlInput.
Waiting for your feedback!
Best regards
Shong
411 Views

Contributor
2011-08-29
10:14 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi Shong,
I do it and my datetime is normal, without .0 in the tLogRow
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|
411 Views

Contributor
2011-08-29
10:44 AM
Author
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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);
Strange...
Perhaps this may help you.
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.
411 Views

Specialist
2011-08-29
10:29 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.)
(Talend does this for you when you use tfileOutput or tLogRow, applying the Date Pattern from the schema.)
411 Views

Specialist III
2012-04-23
12:05 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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?
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?
411 Views

Specialist III
2012-04-23
12:12 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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 -
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)-
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 -
However, when i reformat my date as yyyy-MM-dd, it executes properly. My DBDATE is DMY4
Any idea what's going wrong here?
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?
411 Views

Specialist
2012-04-23
08:17 PM
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.
And you don't need to re-post, you can just edit your previous post if you register.
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.
411 Views
