Talend Context Variable with Date DataType

[DGFUser]

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!

[TRF]

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

[DGFUser]

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

[TRF]

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

[Anonymous]

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) + "'

[DGFUser]

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!

[Anonymous]

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