Hi,

I am reading an input file from SQL database which required date filtering using Talend global context date variables.
The SQL date format is different from the global context variable defined. I am not allowed the define these date fields format in SQL and Talend.

The fields & format :

dbo.gl_entry.input_dt  format is "dd/MM/yyyy HH:mm:ss AM",
context.DATE_START format is "dd-MM-yyyy", and
context.DATE_END format is "dd-MM-yyyy".


The extract of the query command in tMSSQLInput_1 syntax:

"SELECT dbo.gl_entry.input_dt,
            dbo.gl_entry.amount,
        dbo.gl_entry.ccy,
        dbo.gl_entry.deal_no,
        dbo.gl_entry.trans_type
FROM    dbo.gl_entry
WHERE
dbo.gl_entry.input_dt >= '"+TalendDate.formatDate("MM/dd/yyyy HH:mm:ss",context.DATE_START)+"' and
dbo.gl_entry.input_dt <= '"+TalendDate.formatDate("MM/dd/yyyy HH:mm:ss",context.DATE_END)+"'"


Basically, there is no syntax error. However, I am not getting the expected output wanted due to different format in dates.
(Note: the SQL date has the AM/PM format).

Glad if someone can help solve this.

Thank you and regards
KM

Last edited by twitkmg (2011-09-09 16:30:50)