Timestamp Conversion

ady · Post by **ady** » Tue Nov 28, 2006 8:11 am

This is the timestamp i need to convert to load into a OCI 9 stage. Can anyone tell me how to convert it ?... and also what is the ORACLE time stamp format ?

2006-11-22-11.36.51.535212

thanks

chulett · Post by **chulett** » Tue Nov 28, 2006 8:21 am

The answer depends on if you truly mean an Oracle TIMESTAMP field or are trying to populate an Oracle DATE field. The latter is supported natively by the OCI stages, the former is not. I'm assuming you really mean TIMESTAMP.

Since you'll need to fall back on User Defined SQL to leverage the TO_TIMESTAMP() function, your format doesn't really matter as long as it matches the mask in the function call. What you posted should be fine, depending on the precision of the TIMESTAMP field.

ady · Post by **ady** » Tue Nov 28, 2006 8:51 am

I am getting the date mentioned above from a Complex flat file which shows the Native type as "character" and i need to load it into a OCI 9 stage where the table definition shows the field as "Timestamp".

Can i change the type in the OCI table definiton into "DATE" ?. It dosent work right ?

chulett · Post by **chulett** » Tue Nov 28, 2006 9:11 am

Timestamp in DataStage is fine for loading into an Oracle DATE. You've confused me - is the Oracle field a DATE or a TIMESTAMP? Not in the job in the database.

If you are loading a TIMESTAMP then a Char or VarChar in the job should be fine.

ady · Post by **ady** » Tue Nov 28, 2006 12:47 pm

I think the problem is with the length of the original date, it looks like "2006-11-22-11.40.11.801933" . i can get it to look like "2006-11-27 18:07:35 then i think it should work.

chulett · Post by **chulett** » Tue Nov 28, 2006 1:10 pm

Answer the question re: the Oracle field - DATE or TIMESTAMP?

If you hack off the microseconds like you've just shown then it will load into either field, however you are doing yourself a disservice if you do that and load it into a TIMESTAMP field.

ady · Post by **ady** » Wed Nov 29, 2006 8:18 am

The Oracle field is "TIMESTAMP"

chulett · Post by **chulett** » Wed Nov 29, 2006 8:27 am

Ok... then you are ok now? My first post talks about handling a true TIMESTAMP field, do you need more information or are there still doubts? As noted, you do not want to drop the microseconds.

ray.wurlod · Post by **ray.wurlod** » Wed Nov 29, 2006 1:46 pm

beaditya wrote:I think the problem is with the length of the original date, it looks like "2006-11-22-11.40.11.801933" . i can get it to look like "2006-11-27 18:07:35 then i think it should work.

Code: Select all

Left(InLink.TheString,10) : " " : Ereplace(Right(InLink.TheString,15), ".", ":", 2, 1)