Using a global variable in a MSSQL Query generating an error (Date)

[Anonymous]

Hi,

I set a global variable called "lastRunTime" which is a date type read from a csv file.

I get this error every time:

java.sql.SQLException: Conversion failed when converting date and/or time from character string.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2988)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2421)
at net.sourceforge.jtds.jdbc.TdsCore.isDataInResultSet(TdsCore.java:838)
at net.sourceforge.jtds.jdbc.JtdsResultSet.<init>(JtdsResultSet.java:149)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeSQLQuery(JtdsStatement.java:511)
at net.sourceforge.jtds.jdbc.JtdsStatement.executeQuery(JtdsStatement.java:1427)
at local_project.test_0_1.test.tDBInput_1Process(test.java:2255)
at local_project.test_0_1.test.runJobInTOS(test.java:5028)
at local_project.test_0_1.test.main(test.java:4865)

But when print the output of my global variable, i get the correct results.

This is the query i'm running:

SELECT * FROM [customer]
WHERE Insert_date > '"+ TalendDate.formatDate("yyyy-MM-dd HH:mm:ss", (Date)globalMap.get("lastRunTime")) +"'

Can someone please correct me? 

Thank you!

[TRF · Accepted solution]

As your lastRunTime is issued from a CSV file, your global variable should be a String variable and if the column on the DB side is defined as datetime, you have to use STR_TO_DATE function to convert from String to Datetime.

This page may help you for STR_TO_DATE usage https://blogs.msdn.microsoft.com/ssma/2011/06/15/converting-mysql-str_to_date-function/