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
Timestamp datatype
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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
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
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
If this is your DML:
then you have a trailing comma
Code: Select all
insert into datefield values
TO_timestamp(:7, 'YYYY-MM-DD HH24:MI:SS:FF'),
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
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
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