date conversion

[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.

[ray.wurlod]

Try truncating the fractional seconds.

Code: Select all

Left(InLink.TheString, 19)

[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.

[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

[sachin1]

have you tried taking varchar and tested, with varchar it definitely works.

[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.

[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.

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.

[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)