Hi,
I am getting the below error while converting from timestamp to date.
Source is SQL Server having date as time stamp.
Target is Oracle having date.
tst1..Transformer_16: At row 2852, link "DSLink4", while processing column "dtContractExpiration"
Value treated as NULL
Attempt to convert String value "2012-01-23 00:00:00.000" to Date type unsuccessful
Please suggest.
date conversion
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Try truncating the fractional seconds.
Code: Select all
Left(InLink.TheString, 19)
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.
Re: date conversion
i think below is your implementation.
Source(sql server)----> transformer----> OCI stage.
in transformer output stage for date column of oracle, put as VARCHAR with proper length and data will get inserted.
Source(sql server)----> transformer----> OCI stage.
in transformer output stage for date column of oracle, put as VARCHAR with proper length and data will get inserted.
Re: date conversion
But i want it to be a date column in OCI stage and not varchar
Ray i tried using Left (DSlink,9) but still is giving the below error
st1..Transformer_16: At row 2865, link "DSLink4", while processing column "dtContractExpiration"
Value treated as NULL
Attempt to convert String value "2010-02-13" to Date type unsuccessful
Ray i tried using Left (DSlink,9) but still is giving the below error
st1..Transformer_16: At row 2865, link "DSLink4", while processing column "dtContractExpiration"
Value treated as NULL
Attempt to convert String value "2010-02-13" to Date type unsuccessful
Re: date conversion
have you tried taking varchar and tested, with varchar it definitely works.
There's nothing 'definite' about just using a varchar, there's more to it than that.
Seya, what does your target SQL look like, specifically for your DATE field? Obviously, you've got a perfectly valid date format, but you need to make sure the date format you are using matches what the SQL is expecting. Your error indicates it doesn't.
Seya, what does your target SQL look like, specifically for your DATE field? Obviously, you've got a perfectly valid date format, but you need to make sure the date format you are using matches what the SQL is expecting. Your error indicates it doesn't.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Hi Chulett,
i used iconv along with left function to convert to internal format and OCI stage is taking this internal format and uses to_date function to convert to date.
Thanks to all.
i used iconv along with left function to convert to internal format and OCI stage is taking this internal format and uses to_date function to convert to date.
Thanks to all.
chulett wrote:There's nothing 'definite' about just using a varchar, there's more to it than that.
Seya, what does your target SQL look like, specifically for your DATE field? Obviously, you've got a perfectly valid date format, but you need to make sure the date format you are using matches what the SQL is expecting. Your error indicates it doesn't.
Ok... glad you got it working, but just for the record - Oracle does not take 'internal format' dates. Sounds like you've done just what needed to be done: get the date into a external format that matches the format mask used in the TO_DATE() function. For example:
YourDate = '2007-07-25 17:12:13'
TO_DATE(YourDate,'YYYY-MM-DD HH24:MI:SS)
YourDate = '2007-07-25 17:12:13'
TO_DATE(YourDate,'YYYY-MM-DD HH24:MI:SS)
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers