This is the timestamp i need to convert to load into a OCI 9 stage. Can anyone tell me how to convert it ?... and also what is the ORACLE time stamp format ?
2006-11-22-11.36.51.535212
thanks
Timestamp Conversion
Moderators: chulett, rschirm, roy
Timestamp Conversion
Last edited by ady on Tue Nov 28, 2006 8:58 am, edited 1 time in total.
The answer depends on if you truly mean an Oracle TIMESTAMP field or are trying to populate an Oracle DATE field. The latter is supported natively by the OCI stages, the former is not. I'm assuming you really mean TIMESTAMP.
Since you'll need to fall back on User Defined SQL to leverage the TO_TIMESTAMP() function, your format doesn't really matter as long as it matches the mask in the function call. What you posted should be fine, depending on the precision of the TIMESTAMP field.
Since you'll need to fall back on User Defined SQL to leverage the TO_TIMESTAMP() function, your format doesn't really matter as long as it matches the mask in the function call. What you posted should be fine, depending on the precision of the TIMESTAMP field.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Timestamp in DataStage is fine for loading into an Oracle DATE. You've confused me - is the Oracle field a DATE or a TIMESTAMP? Not in the job in the database.
If you are loading a TIMESTAMP then a Char or VarChar in the job should be fine.
If you are loading a TIMESTAMP then a Char or VarChar in the job should be fine.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Answer the question re: the Oracle field - DATE or TIMESTAMP?
If you hack off the microseconds like you've just shown then it will load into either field, however you are doing yourself a disservice if you do that and load it into a TIMESTAMP field.
If you hack off the microseconds like you've just shown then it will load into either field, however you are doing yourself a disservice if you do that and load it into a TIMESTAMP field.
-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:
beaditya wrote:I think the problem is with the length of the original date, it looks like "2006-11-22-11.40.11.801933" . i can get it to look like "2006-11-27 18:07:35 then i think it should work.
Code: Select all
Left(InLink.TheString,10) : " " : Ereplace(Right(InLink.TheString,15), ".", ":", 2, 1)
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.