OCI date problem. Pls guide.

debajitp · Post by **debajitp** » Wed Jul 23, 2003 3:12 am

After searching thru the site, I saw a lot of query regarding this, but could not find a suitable answer. I do not have the PDF documentation to refer to, as sugested by some members. Here is my problem.. (DS 5.2)

My input is OCI8 stage and the column is CHAR(26) field having a value of say, 1998-08-01-14.15.02.178759. My target is a OCI8 stage and the column is a DATE field. I an trying different methods to put the value but nothing seems to work.

I have written a function using Oconv and Iconv to get the date as 1/8/1998 or 01-AUG-1998, but in vain. I tried the built-in transform DateTimeStampToOraOCI(Arg) but that too does not work.

My DATE column is shown as Timestamp in the Data Stage. Even if I make it 'Date' in DS, it does not work.

Any suggestions ? Thanks a lot in advance.

D Paul

uneumann · Post by **uneumann** » Wed Jul 23, 2003 3:26 am

Hi Paul,

the output format must be 'yyyy-mm-dd hh24:mi:ss' (ISO-Format). If you look at the generate SQL from your output you will see that DataStage include automatically a to_char() around you timestamp field.

Regards,
Udo

debajitp · Post by **debajitp** » Wed Jul 23, 2003 3:54 am

I see that a TO_DATE function is called like (TO_DATE(:1, 'YYYY-MM-DD HH24:MI:SS'). So in the transformer I have given a hard coded value (just to test) like '1999-01-01 23:20:20' but then when I run, I get the following error:

ORA-01830: date format picture ends before converting entire input string

regards
D P

quote:Originally posted by uneumann
[br]Hi Paul,

the output format must be 'yyyy-mm-dd hh24:mi:ss' (ISO-Format). If you look at the generate SQL from your output you will see that DataStage include automatically a to_char() around you timestamp field.

Regards,
Udo

D Paul

uneumann · Post by **uneumann** » Wed Jul 23, 2003 6:49 am

Hi Paul,

I couldn't replicate the oracle error. I write a Job that do the same as you discribe and it runs fine.

Regards,
Udo

debajitp · Post by **debajitp** » Wed Jul 23, 2003 7:46 am

Can u pls tell me which function u r using in the transformer ? Thanks.

D Paul

ray.wurlod · Post by **ray.wurlod** » Wed Jul 23, 2003 4:55 pm

The Oracle error message informed you that your date picture ended before converting the entire input string.

Your input string ended at microseconds. Your date picture ended at seconds.

Either you need a different date picture that includes microseconds, or you need an expression to strip the microseconds from the input string. For example Field(InputString,".",1,1) which returns everything to the left of the "." character. This still works on dates for which there is no microseconds component.

Which you choose to do depends on how important the microseconds are in your target data.

Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518