Unable to compare dates in Pentaho Kettle

1
votes

I am using the source data as MySQL database and comparing the dates in the table with system generated dates in Pentaho.

I am using query like below :

Select * FROM trvs_consumer WHERE created_at > ?

value for 'created_at' is like : 2016/07/28 00:00:00.000000000

value for '?' from Pentaho system info is like : 1900/01/01 04:30:00.000

When I run the query, I get the below error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 25

But, when I run the query without the where clause, it runs perfect. Any suggestions ?

1
mysqlpentahokettle
Probably the connection to your system in Pentaho is wrong. – sagi
@sagi, I have checked the detailed logs and found the error as: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 25 Any idea? – Sarang Manjrekar
Can you look at the SQL that was executed? I suspect the date is not being exported in the correct format. – sagi
What step are you using? And is your "created_at" value in a variable or a column from a previous step? – Brian.D.Myers

1 Answers

2
votes

You have to use correct date format to use in mysql query since mysql require it.

Use select values step, and specify correct date format. Here is the example. In 'get system date' step we generate new random date. In 'Select values' step we assign correct date format for mysql. Then we can use this new field in some queries. PDI does not automatically recognize correct date format for db vendor, we have to took care of that manually.

enter image description here