9i milliseconds - how to?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: 9i milliseconds - how to?

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    (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

    Don't blame me, I'm from Red Sox Nation.

  2. #2
    Join Date
    Oct 2000
    Location
    Charlotte, USA
    Posts
    330
    I think you can solve this by using to_char I believe!!!!!!!!!!
    I may be wrong.correct if I am .
    Thanks.
    Thanigaivasan.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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>
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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




    Don't blame me, I'm from Red Sox Nation.

  5. #5
    Join Date
    Mar 2002
    Posts
    48
    For current time, You just insert sysdate. Date is stored as number in Oracle.

  6. #6
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    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
    Don't blame me, I'm from Red Sox Nation.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width