Timestamp datatype

ketfos · Post by **ketfos** » Mon Jul 24, 2006 4:03 pm

Hi,

I have source and target Oracle tables. Both tables have field DeleteTime as datatype Timestamp(6).

How do I populate the target table so that I get the complete date as timestamp(6) i.e 2003-03-26-2.48.07.270954

Thks

Ketfos

ray.wurlod · Post by **ray.wurlod** » Mon Jul 24, 2006 4:13 pm

Easy. You supply a timestamp with microseconds.
There are no inherent data types in server jobs, so you can construct it as a string, and use an appropriate date picture for the TO_DATE function in the INSERT statement.
It's up to you where you get the microseconds from.

kcbland · Post by **kcbland** » Mon Jul 24, 2006 4:14 pm

Are you using the OCI stage with a 9i or 10g database? If so, the generated SQL should work fine.

ketfos · Post by **ketfos** » Mon Jul 24, 2006 4:42 pm

Hi,

I am using ORacle 9i with user defined sql
and use following to transform the date field in my sql statement.

insert into datefield values
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),

I get following warning message follwoed by error message

Stagenametest is not supported for pre-defined update action.

Stagenametest : ORA-01036: illegal variable name/number

Thks

Ketfos

kcbland · Post by **kcbland** » Mon Jul 24, 2006 4:54 pm

It appears you are not passing a properly formatted timestamp string as well as your user-defined SQL does not mask to 6 decimal places.

ketfos · Post by **ketfos** » Mon Jul 24, 2006 5:10 pm

Hi,

What is the meaning of the warning message -

not supported for pre-defined update action.

Thks

Ketfos

kcbland · Post by **kcbland** » Mon Jul 24, 2006 5:36 pm

If this is your DML:

Code: Select all

insert into datefield values 
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),

then you have a trailing comma

ketfos · Post by **ketfos** » Mon Jul 24, 2006 6:03 pm

Hi,

No there is no trailing comma as shown below

VALUES (:1,:2,:3,:4,:5,:6,
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),
TO_DATE(:8, 'YYYY-MM-DD HH24:MI:SS'),
:9,
TO_DATE(:10, 'YYYY-MM-DD HH24:MI:SS'),
TO_DATE(:11, 'YYYY-MM-DD HH24:MI:SS'),
:12,:13,:14,:15,
TO_DATE(:16, 'YYYY-MM-DD HH24:MI:SS'));

ketfos