title: "Incompatible data type in operation" Error when trying to format datetime
body: <p>In ODI, I am fetching a field [datetime] from a table which is in MS SQL server. 
  The datetime from MS SQL is in this format: <strong>2019-06-26 07:41:00.000</strong>.</p>
  
  <p>I need to convert it to this format: 26-06-2019 10:41:00 i.e <strong>DD-MM-YYYY HH:MI:SS</strong> with hour <em>incremented by 3</em>, before writing it to a JSON file. </p>
  
  <p>The corresponding field in JSON is VARCHAR(string). </p>
  
  <p>But I am getting error: <strong><em>Caused By: java.sql.SQLSyntaxErrorException: incompatible data type in operation</em></strong></p>
  
  <p>I gave the following expressing in the ODI 12c mapping attribute expression editor:</p>
  
  <pre><code>TO_CHAR(TO_DATE(NVL(SUBSTR(L_MAILITM_EVENTS.EVENT_GMT_DT, 0, INSTR(L_MAILITM_EVENTS.EVENT_GMT_DT, '.')-1), 
  L_MAILITM_EVENTS.EVENT_GMT_DT ),'YYYY-MM-DD HH24:MI:SS')+ 3/24, 
  'DD-MM-YYYY HH24:MI:SS')
  </code></pre>
  
  <p><a href="https://i.stack.imgur.com/XyHrR.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/XyHrR.png" alt="enter image description here"></a></p>
  
  <p>The entire error:</p>
  
  <blockquote>
    <p>ODI-1228: Task Insert new lines-IKM XML Control Append- fails on the
    target connection IPSEvents_Array_Json. Caused By:
    <strong>java.sql.SQLSyntaxErrorException: incompatible data type in operation</strong>
      at org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)    at
    org.hsqldb.jdbc.JDBCUtil.sqlException(Unknown Source)     at
    org.hsqldb.jdbc.JDBCStatement.fetchResult(Unknown Source)     at
    org.hsqldb.jdbc.JDBCStatement.execute(Unknown Source)     at
    com.sunopsis.jdbc.driver.xml.SnpsXmlStatementRedirector.execute(SnpsXmlStatementRedirector.java:77)
      at
    com.sunopsis.jdbc.driver.xml.SnpsXmlStatement.execute(SnpsXmlStatement.java:31)
      at
    oracle.odi.runtime.agent.execution.sql.SQLCommand.execute(SQLCommand.java:205)
      at
    oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:142)
      at
    oracle.odi.runtime.agent.execution.sql.SQLExecutor.execute(SQLExecutor.java:28)
      at
    oracle.odi.runtime.agent.execution.TaskExecutionHandler.handleTask(TaskExecutionHandler.java:52)
      at
    oracle.odi.runtime.agent.execution.SessionTask.processTask(SessionTask.java:206)
      at
    oracle.odi.runtime.agent.execution.SessionTask.doExecuteTask(SessionTask.java:117)
      at
    oracle.odi.runtime.agent.execution.AbstractSessionTask.execute(AbstractSessionTask.java:886)
      at
    oracle.odi.runtime.agent.execution.SessionExecutor$SerialTrain.runTasks(SessionExecutor.java:2225)
      at
    oracle.odi.runtime.agent.execution.SessionExecutor.executeSession(SessionExecutor.java:610)
      at
    oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:718)
      at
    oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor$1.doAction(TaskExecutorAgentRequestProcessor.java:611)
      at
    oracle.odi.core.persistence.dwgobject.DwgObjectTemplate.execute(DwgObjectTemplate.java:203)
      at
    oracle.odi.runtime.agent.processor.TaskExecutorAgentRequestProcessor.doProcessStartAgentTask(TaskExecutorAgentRequestProcessor.java:800)
      at
    oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor.access$1400(StartSessRequestProcessor.java:74)
      at
    oracle.odi.runtime.agent.processor.impl.StartSessRequestProcessor$StartSessTask.doExecute(StartSessRequestProcessor.java:702)
      at
    oracle.odi.runtime.agent.processor.task.AgentTask.execute(AgentTask.java:180)
      at
    oracle.odi.runtime.agent.support.DefaultAgentTaskExecutor$2.run(DefaultAgentTaskExecutor.java:108)
      at java.lang.Thread.run(Thread.java:748) Caused by:
    org.hsqldb.HsqlException: incompatible data type in operation     at
    org.hsqldb.error.Error.error(Unknown Source)  at
    org.hsqldb.error.Error.error(Unknown Source)  at
    org.hsqldb.FunctionSQL.resolveTypes(Unknown Source)   at
    org.hsqldb.FunctionCustom.resolveTypes(Unknown Source)    at
    org.hsqldb.ExpressionArithmetic.resolveTypes(Unknown Source)  at
    org.hsqldb.FunctionSQL.resolveTypes(Unknown Source)   at
    org.hsqldb.ExpressionLogical.resolveTypes(Unknown Source)     at
    org.hsqldb.ExpressionOp.resolveTypesForCaseWhen(Unknown Source)   at
    org.hsqldb.ExpressionOp.resolveTypes(Unknown Source)  at
    org.hsqldb.FunctionCustom.resolveTypes(Unknown Source)    at
    org.hsqldb.ExpressionArithmetic.resolveTypes(Unknown Source)  at
    org.hsqldb.FunctionCustom.resolveTypes(Unknown Source)    at
    org.hsqldb.QuerySpecification.resolveExpressionTypes(Unknown Source)
      at org.hsqldb.QuerySpecification.resolveTypesPartOne(Unknown Source)
      at org.hsqldb.RangeVariable.resolveRangeTable(Unknown Source)   at
    org.hsqldb.QuerySpecification.resolveRangeVariables(Unknown Source)
      at org.hsqldb.QuerySpecification.resolveReferences(Unknown Source)
      at org.hsqldb.QueryExpression.resolve(Unknown Source)   at
    org.hsqldb.ParserDML.compileInsertStatement(Unknown Source)   at
    org.hsqldb.ParserCommand.compilePart(Unknown Source)  at
    org.hsqldb.ParserCommand.compileStatements(Unknown Source)    at
    org.hsqldb.Session.executeDirectStatement(Unknown Source)     at
    org.hsqldb.Session.execute(Unknown Source)    ... 22 more</p>
  </blockquote>
  
  <p>Where am I going wrong?</p>