text file to oracle date

knowledge · Post by **knowledge** » Wed Aug 18, 2010 8:33 am

Hi ,

I have simple job load data from text file to oracle table.

The dates are coming in following format : 9/16/1989

when I am trying to load into oracle its loading nothing (null) , i tried using iconv , oconv but no use ,

Can you please explain what i have to use , I tried to read iconv and oconv explaination , but if the data is coming into date format in text file do we have to use iconv or oconv ....

Please suggest ...

chulett · Post by **chulett** » Wed Aug 18, 2010 8:37 am

You should use IConv/OConv to convert that external format to YYYY-MM-DD, then append a zero time and use a Timestamp data type. The generated SQL will include the appropriate TO_DATE() function to get it into your DATE field all nice and proper like.

knowledge · Post by **knowledge** » Wed Aug 18, 2010 1:54 pm

Thanks craig,

I used Iconv function , it worked fine ......

If IsNull(trim(FIELDNAME)) then @null else iconv(trim(FIELDNAME),"D/MDY[2,2,4]")

Thanks...

chulett · Post by **chulett** » Wed Aug 18, 2010 5:22 pm

Sorry, but it can't really be "working fine" as Oracle cannot process a DataStage internal date format. However, if the IConv fails, the value will pass through unchanged and so that's probably what is making it look like it is working for you.

You really should have a standard for Oracle dates that doesn't rely on a particular NLS_DATE value. I always convert incoming values to an ISO timestamp in YYYY-MM-DD HH24:MI:SS format and add a zero time for dates that "don't need" a time portion and ensure the SQL has a TO_DATE() wrapper for that column with a matching mask. Works 100% of the time and we can revisit this when you figure out what you're doing isn't quite kosher.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 18, 2010 6:08 pm

If you are using an ODBC stage, then it can accept and properly convert a DataStage internal format date. If you are using an Oracle stage, then you need to present a string in correct timestamp format ("correct" = matching the Oracle DATE picture).

chulett · Post by **chulett** » Wed Aug 18, 2010 7:02 pm

Interesting, wasn't aware of that as I tend to avoid ODBC as much as possible.

ray.wurlod · Post by **ray.wurlod** » Wed Aug 18, 2010 8:04 pm

We did not have any information about which stage types were being used, so I thought I'd throw in that gem. It also depends on whether Data Element "Date" (etc.) is specified whether internal format conversion occurs.

priyadarshikunal · Post by **priyadarshikunal** » Thu Aug 19, 2010 4:42 am

Intresting feature. I tend to use native stages as much as possible. hence, never noticed it.

One more gem from Ray's sleeves. Thanks for letting us know.

knowledge · Post by **knowledge** » Mon Aug 23, 2010 8:17 am

Hi ,

The date is coming in the text file in this format: 2/4/2002

I am convertinbg it into transformer as following :

If IsNull(trim(tdAdmission_From.ReferralDate)) then @null else oconv(iconv(trim(tdAdmission_From.ReferralDate),"D/MDY[2,2,4]"),"D/MDY[2,2,4]"):' ':'00:00:00'

Then standard OCI date format TO_DATE(:20, 'YYYY-MM-DD HH24:MI:SS') to load into target table ...

But it is loading nulls...rejecting rows...ORA-01843: not a valid month

pleASE SUGGEST...

chulett · Post by **chulett** » Mon Aug 23, 2010 8:39 am

Both format masks are wrong. Try:

Code: Select all

OConv(IConv(Trim(tdAdmission_From.ReferralDate),"D"),"D/YMD[4,2,2]"):' 00:00:00'

knowledge · Post by **knowledge** » Mon Aug 23, 2010 9:48 am

Thanks a lot Craig,

I really appreciate ur help ...

It worked fine ...

Only there is small typo in ur code ...the code is as follows for future ref for anyone...

OConv(IConv(Trim(tdAdmission_From.ReferralDate),"D"),"D/YMD[4,2,2]"):' 00:00:00'

thanks again ...

It was very very helpful...

One question:

Why we are using D here ..

format for D is
X = Iconv("31 DEC 1967", "D") X = 0

but my source date is not in 31 DEC 1967 format but 6/11/1992

Thanks ,

chulett · Post by **chulett** » Mon Aug 23, 2010 1:22 pm

D'oh! I fixed my typo, the one I introduced when I went back to capitalize things the way I like. Sorry about that.

That "D" format mask is special and can recognize most "Date" formats without any help from us.

knowledge · Post by **knowledge** » Mon Aug 23, 2010 2:18 pm

Hi,

Date is coming in 9/30/2003 17:30 when I use same function Its not working...just giving me 00:00:00 that means iconv oconv not working ...

Can you please sugest what to use..

Thanks.

arunkumarmm · Post by **arunkumarmm** » Mon Aug 23, 2010 3:17 pm

Iconv(date,"D4") can also work for every date format, except, YYYY-MM-DD. Also, If Iconv doesnt recognize a incoming date, it will not return a zero, it will return NULL instead.

chulett · Post by **chulett** » Mon Aug 23, 2010 4:23 pm

That's not a date, that's some flavor of a timestamp. Strip the date from the time before doing the conversion, me I would use the Field() function for that. Then you get to decide how to handle the time portion when it has been provided to you.