For valid implicit/explicit data type conversion that are allowed please see http://docs.jboss.org/teiid/7.4.0.Final/reference/en-US/html_single/#type_conversions
The error is indicating that, the source is returning a "int" column data, for a column that is mapped as "TIMESTAMP" in the Teiid. Please check you source schema to model mapping in Teiid VDB. Also, remove the columns one by one, and execute in the above SQL to see which column is causing the issue.
This error was resolved using jtds driver for SQL instead of sqljdbc.jar. In fact so many issues are getting resolved if we use jtds driver.
Thanks for the update Mathangi. We have also found JTDS driver much better performing under JBoss AS VM than native one. Also solved couple other issues we faced with native driver.