topic Re: Problem with a date field in my query in Talend Studio

Problem with a date field in my query

stephbzr — Fri, 15 Nov 2024 22:21:31 GMT

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.

Re: Problem with a date field in my query

Anonymous — Wed, 16 Nov 2022 02:56:34 GMT

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

Re: Problem with a date field in my query

stephbzr — Wed, 16 Nov 2022 07:46:37 GMT

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.

Re: Problem with a date field in my query

stephbzr — Wed, 16 Nov 2022 10:04:06 GMT

@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.

Re: Problem with a date field in my query

stephbzr — Wed, 16 Nov 2022 14:51:40 GMT

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

Re: Problem with a date field in my query

Anonymous — Sat, 19 Nov 2022 08:49:52 GMT

what is your target application? Oracle DB?

Re: Problem with a date field in my query

stephbzr — Mon, 21 Nov 2022 08:35:34 GMT

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.