Talend - jdbc generic dateformat using talend/update datetime in sql server and oracle using same tjdbcrow component

Problem:- Update datetime in database tables using tjdbc components. We want our solution to be database independent, therefore we could not use sysdate (oracle) and getdate() (sql server) and similar database functions to get date and also while writing date back to database using tjdbcrow (custom update sql) we faced date format issue. What is way to use generic date format in tjdbc custom sql statement.

Solution

Solution is not specific to talend but to the JDBC drivers support timestamp literals in SQL statements written in the format: {ts 'yyyy-mm-dd hh:mm:ss.f...'. It also supports only date and only time format also.

For us we needed datetime update and we used following in our update statement in our tjdbcrow component to give us generic date

"update tablename set dt_column_name="
+"{ts '" + TalendDate.formatDate("yyyy-MM-dd HH:mm:ss.SSS",TalendDate.getCurrentDate()) + "'}"


this is the sql generated by talend to be processed by jdbc drivers-
update tablename set dt_column_name={ts '2012-01-16 06:54:37.525'}