Problem with a date field in my query

[stephbzr]

Hello, 

I don't understand the following problem I'm having :

I have the below query (with query) to run in my tMap using a tDBInput and the necessary values (boxed in the screenshot). I tested it in the database first.

 

This is a test case to check that the query works and the results I should expect with these values.  So I'm trying to get this one to work in my tMap as below :  

tDBInput

 

tMap

 

But I get no rows even though the values are the same as in my database test case. So to be sure, I output the values I was injecting into the query into an Excel file and I have the result below : 

 

I inject the "right values" normally so I don't understand what the problem is. For information, I tested the query without the "hist_datcpt" field and it worked, I had only 1's as desired. It's the date that is misread, but I don't see what to do about it.

I also tried to set the value to hard and it does not return anything.

[Anonymous]

Hi

Can you have a try to set the hist_datcpt='03/12/2021' in the query? For testing, just link tDBInput to a tLogRow to print the value on the console.

Regards

Shong

[stephbzr]

Hello @Shicong Hong​ 

Thanks for the advice, it is indeed an important test. Here is what I tested in the first instance : 

 

While the same database query gives : 

 

I then tested on Talend without injecting the date, but still displaying the database values and found that the date was displayed in a particular format.

 

So I had the idea to add an SQL function to apply a date format :

And I finally have the expected result in Talend

 

The question now is how can I translate TO_DATE(output_data2.rep_datro, 'DD/MM/RR') into java in my tMap? Here is how my date is generated (from tJavaFlex) in my tMap before being injected into my query. But this is not enough according to what we saw before.

[stephbzr]

@Shicong Hong​ I tried many TalendDate functions because I don't know java, but without success. The only one that works a bit is TalendDate.parseDate(), but I have an error : 

Indeed I have empty date fields when I retrieve my data. 

 

At the moment, I am blocked from reading this date correctly.

[stephbzr]

I tried to create a variable with a different format that looks like tLogRow, this is what I get:

I have 1 for the date 03/12/21, but for the others (01/12/21 or 02/12/21) it doesn't find while in the database and in tLogRow it finds.

 Or with a variable of type date 

 

== same result

haha this makes me completely crazy

[Anonymous]

what is your target application? Oracle DB?

[stephbzr]

Hello @Shicong Hong​ ,

I had found the error, I removed the rownum statement from my request. I went a bit too far in my research when the solution was simple.