-
(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
-
I think you can solve this by using to_char I believe!!!!!!!!!!
I may be wrong.correct if I am .
Thanks.
Thanigaivasan.
-
Code:
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>
-
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
-
For current time, You just insert sysdate. Date is stored as number in Oracle.
-
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
