Page 1 of 1
DB2 Date to SQL Server Date or TimeStamp
Posted: Thu Feb 02, 2006 9:36 am
by Titto
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
Posted: Thu Feb 02, 2006 2:25 pm
by ray.wurlod
SQL Server expects milliseconds. Concatenate ".000" to your timestamp.
Posted: Thu Feb 02, 2006 4:21 pm
by Titto
Hi Ray,
what would be the Data type in SQL server side.
But source is Date not Timestamp.
Thanks
Posted: Thu Feb 02, 2006 10:28 pm
by ray.wurlod
Then concatenate " 00:00:00.000".
Posted: Fri Feb 03, 2006 10:50 am
by Titto
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.
Posted: Fri Feb 03, 2006 2:39 pm
by ray.wurlod
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
Posted: Fri Feb 03, 2006 8:31 pm
by bmadhav
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.
Posted: Sun Feb 05, 2006 10:58 am
by peterbaun
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