Page 1 of 1
error while populating date value from seq file to oracle
Posted: Mon Dec 04, 2006 5:11 am
by 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
Posted: Mon Dec 04, 2006 5:43 am
by balajisr
Can you post your ICONV and OCONV code?
Posted: Mon Dec 04, 2006 5:53 am
by 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
Posted: Mon Dec 04, 2006 6:14 am
by 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?
Posted: Mon Dec 04, 2006 6:35 am
by 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
Posted: Mon Dec 04, 2006 6:53 am
by DSguru2B
Excuse my ignorance, but doesnt Oracle take in the timestamp as
YYYY-MM-DD ?
Posted: Mon Dec 04, 2006 8:06 am
by 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
Posted: Mon Dec 04, 2006 8:08 am
by 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.
Posted: Mon Dec 04, 2006 4:06 pm
by ray.wurlod
rkdatastage wrote: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?