error while populating date value from seq file to oracle

[rkdatastage]

Hi all

I am trying to load a column which is of date datatype

i had encounter this error

can any one share your views what might be the mistake

Error message :
ob1..Transformer_1: At row 1, link "DSLink4", while processing column "datett"
Value treated as NULL
Attempt to convert String value "22-nov-04" to Date type unsuccessful

I am very much clear that i had to use Iconv & Oconv function to convert this into valid date format

but still i am facing the problem

Kindly help me out

Thanks in advance
RK

[balajisr]

Can you post your ICONV and OCONV code?

[rkdatastage]

Thanks for your response

my code :

Oconv(Iconv(DSLink3.date,"D-DMY"),"D-DMY[2,A3,4]")

My source file structure
no,"date"
1,"22-nov-04"

my output is : 1,0022-11-20 04:00:00

I had specified my target table date column datatype as timestamp

Thanks in advance
RK

[ray.wurlod]

You seem to have an offset issue - I can see the date in the timestamp. However, you have not provided your entire derivation - for example how does the time component get there?

Try doing one piece at a time, to see where the problem is actually occurring. Just Iconv() first, which should yield a signed integer. Then Oconv(). Then feeding it to Oracle.

Are you using generated or user-defined SQL?

[rkdatastage]

Thanks ray for your response

my intention is to store the date value into the database

i had tried with changing the data type of date column from timestamp to date
but still i am unable to populate the date value into table

i had redirected the output to a flat file and it is having this data

1,22-Nov-2004

and my source file is like this
no,"date"
1,"22-nov-04"

my code is :
Oconv(Iconv(DSLink3.date,"D-DMY"),"D/DMY[2,2,4]")

kindly guide me

Thanks in advance
RK

[DSguru2B]

Excuse my ignorance, but doesnt Oracle take in the timestamp as
YYYY-MM-DD ?

[rkdatastage]

thanks for your valid response

i had resolved my problem

the Oconv conversion format is wrong

i had tried with this code

Oconv(Iconv(DSLink3.date,"D-DMY"),"D/YMD[4,2,2]")

and it worked as i changed the date column datatype as timestamp

if any chance can anybody throw me a light that how can i load the date values using date as my target datatype column

Thanks in advance
RK

[chulett]

With generated sql, yes, it wants an ISO standard value: YYYY-MM-DD. And there's only about a billion posts on this, including multiple versions of my standard rant on the subject.

I should write a frelling FRAQ.

[ray.wurlod]

my output is : 1,0022-11-20 04:00:00

So where did that string come from which, you can see, contains the string 22-11-20 04?