topic Re: Talend Context Variable with Date DataType in Talend Studio

Talend Context Variable with Date DataType

DGFUser — Sat, 16 Nov 2024 05:25:42 GMT

Hello Everyone!

I have been using Talend's Context variables for a while; however, I am now running into an issue as I have never used a variable with Date Datatype before. I have a field in the database called my_date and it is in the format of "DD-MON-YY" and I have created a context variable withe date datatype: context.CLOSING_MONTH and below is my query

select  id,due_date

 from my_table

 where trunc(due_date) <  '"+context.CLOSING_MONTH+"'

however, I have been getting the following error:

java.sql.SQLDataException: ORA-01858: a non-numeric character was found where a numeric was expected

the value of context.CLOSING_MONTH is: 2019-07-01 00:00:00

the odd thing is that if I remove the context variable and put its exact value (hard coding it) the error disappears.

Your help is really appreciated!

Re: Talend Context Variable with Date DataType

TRF — Sun, 30 Jun 2019 11:40:45 GMT

Just declare your context variable as a string variable then use the TO_DATE oracle function in your SQL query

Re: Talend Context Variable with Date DataType

DGFUser — Sun, 30 Jun 2019 13:52:51 GMT

This is what I did; however, I am still getting the same error.

Re: Talend Context Variable with Date DataType

TRF — Sun, 30 Jun 2019 14:48:31 GMT

Can't see the TO_DATE function called in your initial query. Can you share it?

Re: Talend Context Variable with Date DataType

Anonymous — Sun, 30 Jun 2019 21:42:17 GMT

The data type of the context variable CLOSING_MONTH is of Date type right?

To use this value in your query you have to format the Date typed value into a compatible String for your query.

You can do that with the routine TalendDate.

select id, due_date
 from my_table
 where trunc(due_date) < '" + TalendDate.formatDate("yyyy-MM-dd", context.CLOSING_MONTH) + "'

Re: Talend Context Variable with Date DataType

DGFUser — Mon, 01 Jul 2019 09:11:53 GMT

Thank you all for your responses. I believe I figured out what the issue is... It seems that if I write my query in the builder and click on OK Talends inserts '/' and my query looks like the following:

select  id,due_date

 from my_table

 where trunc(due_date) <  '\"+context.CLOSING_MONTH+\"'

which causes the issue.. it is really odd that it is being added and thankfully I was able to spot it.

Thanks again as your help is really appreciated!

Re: Talend Context Variable with Date DataType

Anonymous — Mon, 01 Jul 2019 09:29:22 GMT

I never use the SQL builder. I use SQL tools like DBeaver to create the queries and apply them in the component manually.