Oracle datetime field conversion

Aggie99 · Post by **Aggie99** » Mon Aug 24, 2009 10:18 pm

I have a datetime field that looks like
"12-JUL-09 07.02.00.000000 AM" and store in a csv file (that is the source file).

My target is an Oracle table. the above field is defined as "timestamp" in the Oracle table.

I have problem loading the source field into the target table. What do I need to do so I can load this datetime field into Oracle?

Please help.

dxk9 · Post by **dxk9** » Mon Aug 24, 2009 11:00 pm

Hi,
If all the data in that field is of "12-JUL-09 07.02.00.000000 AM" format (I mean fixed length), then you can convert the data to "12-JUL-2009 07:02:00 AM" in the transformer using string functions and the use string to timestamp function.

Regards,
Divya

ray.wurlod · Post by **ray.wurlod** » Mon Aug 24, 2009 11:05 pm

You need to transform whatever format is coming in into the format expected by the default Oracle timestamp picture (or supply a non-default picture).

Aggie99 · Post by **Aggie99** » Tue Aug 25, 2009 5:00 am

dxk, can you give me an example of what you mean. yes, all the records have are fixed lengt.

ray, the format is already Oracle native format. I can use sqlldr to load the file, but I can't load using datastage Oracle Enterprise Stage.

Stages that I used:

Sequential file (read csv in) -> Transformer -> Oracle Enterprise Stage (load to target table). What should I specify as the datetime format, I am a bit lost in the translation.

chulett · Post by **chulett** » Tue Aug 25, 2009 5:24 am

What is your target field - a DATE or a TIMESTAMP? If the latter, what is the precision of the timestamp?

And there's no such thing as "Oracle native format" unless you're trying to say it matches the current NLS_DATE_FORMAT or NLS_TIMESTAMP_FORMAT for the target database.

dxk9 · Post by **dxk9** » Tue Aug 25, 2009 5:37 am

Hi,

dxk9 wrote:Hi,
If all the data in that field is of "12-JUL-09 07.02.00.000000 AM" format (I mean fixed length), then you can convert the data to "12-JUL-2009 07:02:00 AM" in the transformer using string functions and the use string to timestamp function.

Say for eg: you have date as "12-JUL-09 07.02.00.000000 AM", you can import the data as string(varchar) prior to join, then you can do the join. In this case, if this is not matching with the left side table, this field will return a NULL value. Then you can use a transformer post the join and convert the field to timestamp using stringtotimestamp() function.

It will be something like:

If isnull(<input_date_field>) then setnull() else if len(trim<input_date_field>)=0 then setnull() else stringtotimestamp(<input>,<format>)

Remember that before using the above conversion, you should first convert the "12-JUL-09 07.02.00.000000 AM" to "12-JUL-09 07:02:00 AM" using string functions like LEFT,CONCATENATE (:),RIGHT in transformer.

Regards,
Divya

chulett · Post by **chulett** » Tue Aug 25, 2009 5:42 am

Where did 'join' come from? This is about 'load' not 'join'.

priyadarshikunal · Post by **priyadarshikunal** » Wed Aug 26, 2009 10:00 am

why you need to remove microsecond part only to load that value?

assuming precision as 6

you just need to use ".6" after "ss" in date mask in transformer, mention scale as 6 in column property thats all.

in custom sql you need to use to_timestamp(input,format)

in format mention ".ff6" after ss in the date format.