tMSSqlInput query command to filter records by dates problem

Anonymous — Sat, 16 Nov 2024 12:41:44 GMT

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

Re: tMSSqlInput query command to filter records by dates problem

Anonymous — Fri, 09 Sep 2011 17:06:24 GMT

I believe the problem is in your format "MM/dd/yyyy HH:mm:ss" MySQL will only recognize a form of "yyyy/MM/dd HH:mm:ss"
http://dev.mysql.com/doc/refman/5.5/en/datetime.html

Re: tMSSqlInput query command to filter records by dates problem

Anonymous — Mon, 12 Sep 2011 04:10:49 GMT

Hello jandry.,
I've tried using format "yyyy/MM/dd HH:mm:ss" on query SQL table, it doesn't work, perhaps it's for MySQL only.
Any other suggestion?
Thanks & regards,
Km

topic Re: tMSSqlInput query command to filter records by dates problem in Talend Studio

tMSSqlInput query command to filter records by dates problem

Re: tMSSqlInput query command to filter records by dates problem

Re: tMSSqlInput query command to filter records by dates problem