KB439884: “An invalid datetime format was detected” error occurs after migrating a datamart freeform SQL report between environments

Symptom 

“An invalid datetime format was detected” error occurs after migrating a datamart freeform SQL report using object manager between environments as shown below:
 
Error type: Odbc error. Odbc operation attempted: SQLExecute. [22007:-99999: on SQLHANDLE] [IBM][CLI Driver] CLI0113E SQLSTATE 22007: An invalid datetime format was detected; that is, an invalid string representation or value was specified. SQLSTATE=22007 Connection String: DSN=xxx;UID=xxx;PROTOCOL=TCPIP;PORT=xxx;HOSTNAME=xxx;DATABASE=xxx;. SQL Statement: insert into xxx values (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?).
2017-06-27 10:04:01.018+01:00 [HOST:xxx][SERVER:CastorServer][PID:1412][THR:512][Query Engine][Error][0x80042300] QueryEngine encountered error: OdbcParameterizedQuery::ExecQuery failed. 
Error type: Odbc error. Odbc operation attempted: SQLExecute. [22007:-99999: on SQLHANDLE] . 
 
It is noticed during temp table generation pass; one column’s datatype and order is changed.
 
 

Cause 

This is a known issue.
  

Action

Currently this issue is still being reviewed for feasibility by our Technology team and it is not scoped for any upcoming scheduled Strategy release.
 
 
 

Workaround 

The VLDB setting Datamart Column Order by default uses option 1, knowing this if all the local attributes of the freeform SQL report have the same attribute weight which is 0, then the order of the SQL generated columns would depend on how the SQL Engine gets them from the collection it gets from COM.
 
Datamart Column Order:

1. Columns created in order based on attribute weight (default)
2. Columns created in order in which they appear on the template

 
Switch to option 2 fixed the temp table column order.