Sarang Sarang - 9 days ago 6
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

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

Comments