dot zero in a end of a datetime

One Star

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 )
((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.
Community Manager

Re: dot zero in a end of a datetime

Hi
For testing, link it to tLogRow from tMysqlInput and see whether it comes from tMysqlInput.
Waiting for your feedback!
Best regards
Shong
----------------------------------------------------------
Talend | Data Agility for Modern Business
One Star

Re: dot zero in a end of a datetime

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|
One Star

Re: dot zero in a end of a datetime

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.
Seven Stars

Re: dot zero in a end of a datetime

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.)
One Star

Re: dot zero in a end of a datetime

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?
One Star

Re: dot zero in a end of a datetime

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?
Seven Stars

Re: dot zero in a end of a datetime

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.

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Downloads and Trials

Test drive Talend's enterprise products.

Downloads