I don't understand!!

ajongba · Post by **ajongba** » Mon Apr 30, 2007 1:56 am

Hi all
I have a problem where i am unable to insert into oracle9i.The source is a fixed width file where the date is in this format 20050101(example date) and my target is suppose to be a date datatype. I tried using the Iconv and Oconv functions but i think i m missing out on something .The error message that i get is "a non-numeric character found where a numeric character was expected" Please help me with this problem. The message that appears when proper Iconv and Oconv is used is "Attempt to convert String value "01/01/2005" to Date type unsuccessful"

Somebody please help me

rafik2k · Post by **rafik2k** » Mon Apr 30, 2007 2:24 am

First check the date format in the target database.

Try to insert one record directly with date '01/01/2005' using sql command, see the output.

I believe your target table has different date format.

ajongba · Post by **ajongba** » Mon Apr 30, 2007 2:43 am

i tried inserting the date 01/01/2005 by using the sql "insert into tablename(fmov)values (01/01/2005)" and it throws an error "not a valid month"

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 4:49 am

Maybe your Oracle date picture is set up to require an alphabetic month. You can use TO_DATE function in Oracle INSERT statement to "translate" from other formats, or generate a date in the expected format. Find out (perhaps using a SELECT statement) what your default Oracle date picture is.

ajongba · Post by **ajongba** » Mon Apr 30, 2007 5:29 am

ray.wurlod wrote:Maybe your Oracle date picture is set up to require an alphabetic month. You can use TO_DATE function in Oracle INSERT statement to "translate" from other formats, or generate a date in the expected ...

I tried inserting by using the sql command as "insert into tablename(columnname)values ('01-jan-2005') " and it worked...Can anyone tell me the format which will change the string format 20050101 into '01-jan-2005'...

ray.wurlod · Post by **ray.wurlod** » Mon Apr 30, 2007 5:32 am

One which will is

Code: Select all

Oconv(Iconv(InLink.TheDate,"DYMD"),"D-DMY[2,A3,4]")

ajongba · Post by **ajongba** » Mon Apr 30, 2007 5:47 am

ray.wurlod wrote:One which will is
Code: Select all
Oconv(Iconv(InLink.TheDate,"DYMD"),"D-DMY[2,A3,4]")
...

I don't get it...... I used the code and it is still not inserting....I get a warning message saying "Attempt to convert String value "03-JAN-2005" to Date type unsuccessful"...What is going on???? Please help

ajongba · Post by **ajongba** » Mon Apr 30, 2007 6:12 am

I tried by changing the datatype to varchar and i got a warning as "ORA-01858: a non-numeric character was found where a numeric was expected"....What is going on????

chulett · Post by **chulett** » Mon Apr 30, 2007 6:59 am

ray.wurlod wrote:Maybe your Oracle date picture is set up to require an alphabetic month.

There really is no such thing. An Oracle date is just a date stored in internal format and never requires any particular format. You just need to be able to tell it what format your data is in, that's what the TO_DATE function is for. Get it wrong and you'll see the errors you've been seeing.

You are thinking of the NLS_DATE format, which is just the default format for the database and what it uses if it has no clue what else to do. That's why a Varchar can 'work' if you match that format, which is typically DD-MMM-YYYY I believe. However, I've explained here a bajillion times why I firmly belive that to be a Very Bad Idea as to an approach.

ajongba - here's a solution for you.

1) Declare your field a Timestamp in the job of at least 19 characters.
2) Use this derivation:

Code: Select all

Oconv(Iconv(InLink.TheDate,"DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

3) Let the stage generate the sql for you. If for some reason you insist on using your own, make sure this is wrapped around the values clause for the date field:

Code: Select all

TO_DATE(YourField,"YYYY-MM-DD HH24:MI:SS")

You'll be fine as long as your incoming dates are valid.

ajongba · Post by **ajongba** » Tue May 01, 2007 10:42 pm

[quote="chulett
ajongba - here's a solution for you.

1) Declare your field a Timestamp in the job of at least 19 characters.
2) Use this derivation:

Code: Select all

Oconv(Iconv(InLink.TheDate,"DYMD"),"D-YMD[4,2,2]") : " 00:00:00"

3) Let the stage generate the sql for you. If for some reason you insist on using your own, make sure this is wrapped around the values clause for the date field:

Code: Select all

TO_DATE(YourField,"YYYY-MM-DD HH24:MI:SS")

You'll be fine as long as your incoming dates are valid.

[/quote]

Hey!!! :D It worked !!!!
Thankyou for the effort put by you guys.... all of you