Decimal To Timestamp conversion error

abhilashnair · Post by **abhilashnair** » Wed Mar 07, 2012 9:40 pm

I am using ODBC connector stages in my job. The job has source,transformer and target. Source ODBC connector has db2 database and target is SQL Server
There are 10 columns in the source which is of data type Decimal(8,0). These contain dates in yyyymmdd format
In the target these columns are of datetime format. In the transformer I am using DecimalToTimestamp(colname,"%yyyy%mm%dd").
However I am getting error [ODBC SQL Server Driver] Invalid Date format
When I replace target ODBC connector with ODBC Enterprise stage the error goes away.
Is this some kind of bug in ODBC connector

qt_ky · Post by **qt_ky** » Wed Mar 07, 2012 11:05 pm

I am guessing the Connector is more particular than the Enterprise stage was. If your target type is datetime then it's probably expecting to get the time from the decimal value in addition to the date. One way to test is if your decimal needs 6 more zeros, i.e. Decimal(14,0), then you could multiply each source column: colname * 1000000 within the function.

kwwilliams · Post by **kwwilliams** » Thu Mar 08, 2012 7:50 am

You are correct, the connector is much more strict about metadata stages than the previous enterprise stages had been. However, using correct metadata has always been a best practice - a timestamp is not a decimal. Change your data type to timestamp.