topic Context variables - can't select only the date (without its format) in Talend Studio

Context variables - can't select only the date (without its format)

Anonymous — Sat, 16 Nov 2024 13:04:26 GMT

Trying to select a date in the Context tab. Can?t just enter the date, must use the pop-up calendar. This brings in both the date and its format (yyyy-MM-dd;2011-02-22) which does not work when the context variable is used in a query.
Workaround: Define the context variable as a string but in the Oracle date format:
?dd-MON-yyyy?, e.g., ?22-FEB-2011?
Should I be doing something different in using the pop-up calendar?

Re: Context variables - can't select only the date (without its format)

Anonymous — Fri, 25 Feb 2011 09:34:13 GMT

Hi
Define the context variable as a string, and then parse it to a Date with specify format, eg:
"select * from tableName where mydate="+TalendDate.parseDate("dd-MMM-yyyy", context.mydate)
Best regards
Shong

Re: Context variables - can't select only the date (without its format)

Anonymous — Fri, 25 Feb 2011 12:14:39 GMT

Thanks for the info -
Is this a bug?

Re: Context variables - can't select only the date (without its format)

Anonymous — Fri, 25 Feb 2011 16:06:44 GMT

Not much luck. Here are some errors. The query is in a tOracleInput comonent. The context.TheDate is 'dd-Mon-yyyy' format, including the quotes.

SELECT *
FROM table
WHERE Table.LAST_MOD_DATE
>"+TalendDate.parseDate("dd-MMM-yyyy",context.TheDate)
statistics] connecting to socket on port 3459
connected
Exception in component tOracleInput_2
java.sql.SQLException: ORA-00933: SQL command not properly ended
=================================
SELECT *
FROM table
WHERE Table.LAST_MOD_DATE
> '"+TalendDate.parseDate("dd-MMM-yyyy",context.TheDate)+"'"
Exception in component tOracleInput_2
java.sql.SQLException: ORA-01858: a non-numeric character was found where a numeric was expected
at oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:331)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:288)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:745)

Re: Context variables - can't select only the date (without its format)

Anonymous — Fri, 25 Feb 2011 16:35:35 GMT

print your query to the console in a tJava to debug. Chances are you dont have the format exactly right.

Re: Context variables - can't select only the date (without its format)

alevy — Fri, 04 Mar 2011 04:51:39 GMT

If you're using the "date" as part of a SQL statement, it should be a string not a date i.e.
- if your context variable is a string (in the correct format): "SELECT * FROM table WHERE Table.LAST_MOD_DATE>'"+context.TheDate+"'"
- if your context variable is a date: "SELECT * FROM table WHERE Table.LAST_MOD_DATE >'"+TalendDate.formatDate("dd-MMM-yyyy",context.TheDate)+"'"
In both cases, don't forget the '' date-delimiters for Oracle.

Re: Context variables - can't select only the date (without its format)

Anonymous — Wed, 09 Mar 2011 18:42:32 GMT

Thanks, alevy. Your context variable date version is just what we were looking for. We recognized the SQL string need a date in the form of a string, but wanted the flexibility of setting the context variable using a calendar popup.
It seems date/time pattern in the context variable screens serve no purpose. When I looked at the context variable in the SQL string Talend created I get:
<context variable = "Wed, 9 Mar 2011 12:08:56 -0700"
regardless of what format string was provided. But formatting the date via the TalendDate.formatDate() method works.

Re: Context variables - can't select only the date (without its format)

alevy — Wed, 09 Mar 2011 23:14:44 GMT

I believe the pattern only becomes relevant when you try to use tContextLoad or tContextDump - the string is parsed or formatted (respectively) using the pattern defined with the default value. This is basically the same principle as for schema dates i.e. the pattern is a mask only.
There are some issues with context date patterns - see https://community.talend.com/t5/Design-and-Development/how-to-give-column-names-through-context/td-p/101085.