DB2 Date to SQL Server Date or TimeStamp

Titto · Post by **Titto** » Thu Feb 02, 2006 9:36 am

I am loading data from DB2 to SQL Server, I defined the SQL Server column as Date type as DATE, but then i move data DB2 Date field to Sql Server Date fields it is aborting. I tried SQL Server column defining TimeStamp it worked with Current TimeStamp.
So, my question is what is best SQL server data type I need to use and How to convert DB2 Date to Timestamp so that i can use it to insert in SQL Server. ( I am new to sql server)

Any Help is appreciated!

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Feb 02, 2006 2:25 pm

SQL Server expects milliseconds. Concatenate ".000" to your timestamp.

Titto · Post by **Titto** » Thu Feb 02, 2006 4:21 pm

Hi Ray,
what would be the Data type in SQL server side.
But source is Date not Timestamp.

Thanks

ray.wurlod · Post by **ray.wurlod** » Thu Feb 02, 2006 10:28 pm

Then concatenate " 00:00:00.000".

Titto · Post by **Titto** » Fri Feb 03, 2006 10:50 am

Thanks Ray,

I am not sure will it store as timestamp or Char filed if i concatenate "00:00:00.000" and move the filed to SqlServer. If it stored as Char - i hope we may not able to do data functions on this field.

But I did defined the SqlServer column data type as Chat and selected Data element as "SQL.SMALLDATETIME". By doing this i could able to insert the Date from DB2 by doing Oconv on Input data column.

Hope i am doing right, need to do some Data calculation on SqlServer Date filed.

Let me know if i did wrong by following the above.

Thanks.

ray.wurlod · Post by **ray.wurlod** » Fri Feb 03, 2006 2:39 pm

There are no SQL data types within server jobs. This means that, even though you perform a "string" operation, if the metadata specify that the column is TimeStamp, it will be stored as TimeStamp.
:D

bmadhav · Post by **bmadhav** » Fri Feb 03, 2006 8:31 pm

First, make sure the DB2 date is in a format of mm/dd/ccyy. Then in the transformer right before the load to SQL sever, use the following datatype in "data element" column:
SQL.SMALLDATETIME. The length is 16 and display size is 19.
using this datatype in the target column, will make ensure u convert DB2 date to SQL server date time.

peterbaun · Post by **peterbaun** » Sun Feb 05, 2006 10:58 am

Hi

Don't know if you have got this working yet. The DB2 date is probably a day number when you extract it so you need to convert it to a correct date format.

We have some DS jobs doing this conversion and the following works :
Oconv(Arg1, "D-YMD[4,2,2]") : " 00:00:00.000"

Regards
Peter