Sarang Sarang - 1 year ago 134
MySQL Question

Unable to compare dates in Pentaho Kettle

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 ?

Answer Source

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

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download