how to handle sql server timestamp data type?

fyaqq · Post by **fyaqq** » Mon Dec 18, 2006 12:44 am

I have a table in sql server in which there is a column called TMStamp. It is
defined as binary(8), and it is supposed to store the time a row is modified.

Now in the transformer I want to convert it into a timestamp data type and store in my oracle table. I have no idea how to set this up in the transformer.

Please shed some light on this! Thanks

chulett · Post by **chulett** » Mon Dec 18, 2006 12:49 am

The Oracle part is easy. Just need to know what kind of field you need to store it in - a DATE or a TIMESTAMP of some kind?

However, the binary(8) part sounds like a 'special' storage format, unless that's normal for SQL Server? If it is 'special', do you know how to translate whatever output you are getting into a normal ISO timestamp?

fyaqq · Post by **fyaqq** » Mon Dec 18, 2006 1:22 am

I don't know how to deal with the binary(8) from the sql server. It isn't even displayed in a readable format. I think it is a standard sql server data type.

chulett wrote:The Oracle part is easy. Just need to know what kind of field you need to store it in - a DATE or a TIMESTAMP of some kind?

However, the binary(8) part sounds like a 'special' storage format, unless that's normal for SQL Server? If it is 'special', do you know how to translate whatever output you are getting into a normal ISO timestamp?

[/code]

chulett · Post by **chulett** » Mon Dec 18, 2006 8:07 am

Then check with your DBA, ask them how it is normally handled. If that's the case, they can give you the sql you'd need to extract it in a readable format I would think. Otherwise it sounds like you'd have to pull out the binary field and then apply some kind of 'business rule' to decode it. For that, whomever architected the DB for you should be able to help.

ps. You need to enable the Disable Smilies in this post option whenever you start sticking numbers or other symbols inside parenthesis. Otherwise things like an 8 followed by a right paren become Mr Cool.

That's why my post shows it correctly and your quote of it goofed it up.

ray.wurlod · Post by **ray.wurlod** » Mon Dec 18, 2006 2:42 pm

Assuming you can read the Binary(8) column at all into DataStage, you may be able to convert it using Iconv(InLink.TheColumn, "MB"). Try reading it as a Char(8) if Binary(8) does not work, in which case use "MX0C" as the second argument of the Iconv() function. That will give you the content.

You will need to know what lives where to convert it to a timestamp. What shows if you SELECT this column using isql? Could you perhaps CAST the column as BigInt as part of the extraction SQL?