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
how to handle sql server timestamp data type?
Moderators: chulett, rschirm, roy
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?
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?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
[/code]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?
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.
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.
Last edited by chulett on Mon Dec 18, 2006 4:10 pm, edited 1 time in total.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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?
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?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.