oci can't handle miliseconds
Moderators: chulett, rschirm, roy
oci can't handle miliseconds
Hi,
I am getting the following error when I try to load a timestamp with miliseconds:
ORA-01830: date format picture ends before converting entire input string
I manipulate the date to look like this: yyyymmdd hh24:mi:ss.MMM
I am using OCI9 and have the field defined as timestamp.
Thanks for any suggestions
Randy
I am getting the following error when I try to load a timestamp with miliseconds:
ORA-01830: date format picture ends before converting entire input string
I manipulate the date to look like this: yyyymmdd hh24:mi:ss.MMM
I am using OCI9 and have the field defined as timestamp.
Thanks for any suggestions
Randy
I tried doing that and got the same error message. Then I trimmed off the milliseconds as it was not of importance to the user. Before you want to do anything, I would suggest you to findout if milliseconds data is really important for the client or would it be ok if you skipped it.
If you want to trim the milliseconds then use the Field function in the Transformer Derivation. Hope that helps.
Kris
If you want to trim the milliseconds then use the Field function in the Transformer Derivation. Hope that helps.
Kris
-
- Premium Member
- Posts: 1255
- Joined: Wed Feb 02, 2005 11:54 am
- Location: United States of America
Re: oci can't handle miliseconds
Did you try setting the scale to 3?
gateleys
gateleys
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Yes, that's what you have to do. The OCI stage doesn't natively support the Oracle TIMESTAMP datatype, only DATE, so you need to wrap it up yourself with an appropriate 'TO_TIMESTAMP' function call that matches the way the TIMESTAMP was declared in the target table.randy wrote:not real elegant, but it works.
You may also be able to get away with treating it as a Varchar and making sure your data is in the correct format but to me the TO_TIMESTAMP is more 'appropriate' in spite of the custom sql that is (by necessity right now) involved.
Ray, the FF part is not limited to milliseconds but to the fractional precision declared when the TIMESTAMP was created - and the fractional seconds precision can range from 0 to 9 digits, from what I recall.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers