9i milliseconds - how to?

[gopi]

(9i)

I know I have asked this before with different wording and I have also looked into the new TIMESTAMP data type in the doc but I need to ask again.

How do I insert milliseconds into a column defined as a time stamp?

This did not work:

create table temp_table (col1 timestamp);


insert into temp_table (COL1) values
(to_timestamp('1999-12-01 11:30:55.3','YYYY-MM-DD HH:MI:SS.S'));

ORA-01810 date format not recongized


insert into temp_table values
(TO_date('1999-12-01 11:30:55.3','YYYY-MM-DD HH24:MI:SS.S'))

ORA-01810 date format not recongized

[thanigai]

I think you can solve this by using to_char I believe!!!!!!!!!!
I may be wrong.correct if I am .
Thanks.
Thanigaivasan.

[jmodic]

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> create table temp_table (col1 timestamp);

Table created.

SQL> insert into temp_table (COL1) values 
  2  (timestamp '1999-12-01 11:30:55.3');

1 row created.

SQL> select * from temp_table;

COL1
----------------------------------------
01.12.99 11:30:55,300000

SQL>

[gopi]

Thanks Jurij! That really helped me move forward.

How would I insert the CURRENT time with milliseconds into a table? The following code does NOT work but please consider it as psdo-code (what I am trying to achieve).

insert into temp_table (COL1) values (timestamp SUBSTR(CURRENT_TIMESTAMP,1,26));


--- This is what I want to get into COL1 rather than a hard coded string:

SELECT substr(CURRENT_TIMESTAMP,1, 26) FROM DUAL

SUBSTR(CURRENT_TIMESTAMP,1,26)
-------------------------------------------
19-JUN-02 10.48.49.282927

[vanirame]

For current time, You just insert sysdate. Date is stored as number in Oracle.

[gopi]

vanirame,

sysdate did not work - it did not put in milliseconds:

Insert into temp_table (COL1) values (sysdate);

Does not insert the miilseconds into the timestamp column, COL1 (see row number3).

SQL> select col1 from temp_table;

01-DEC-99 11.30.55.300000 AM
01-DEC-99 11.30.55.300000 AM
19-JUN-02 01.58.13.000000 PM