Page 1 of 1
date conversion
Posted: Tue Jul 24, 2007 3:45 am
by Seya
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.
Posted: Tue Jul 24, 2007 4:11 am
by ray.wurlod
Try truncating the fractional seconds.
Re: date conversion
Posted: Tue Jul 24, 2007 4:45 am
by sachin1
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.
Re: date conversion
Posted: Tue Jul 24, 2007 4:55 am
by Seya
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
Re: date conversion
Posted: Tue Jul 24, 2007 5:44 am
by sachin1
have you tried taking varchar and tested, with varchar it definitely works.
Posted: Tue Jul 24, 2007 6:12 am
by chulett
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.
Posted: Wed Jul 25, 2007 3:15 am
by Seya
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.
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.
Posted: Wed Jul 25, 2007 7:18 am
by chulett
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)