DATE type finer than seconds
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: DATE type finer than seconds

  1. #1
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    (9i, Solaris)
    Can I get a date more refined than the second?

    ie, more refined than:
    to_char(SYSDATE,'YYYYMMDDHH24MISS')

    If not, anything creative?
    Don't blame me, I'm from Red Sox Nation.

  2. #2
    Join Date
    Jan 2002
    Posts
    152
    Select dbms_utility.get_time from dual;

    It shows the time in miliseconds...

  3. #3
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    there are new data types that supports milliseconds, look for timestamp datatypes

  4. #4
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Okay friends,

    I have taken both your advice:

    SQL> Select to_char(sysdate,'yyyymmdd hh24:mi:ss'),dbms_utility.get_time from dual;


    20020606 15:27:51
    -740047998

    - AND -

    DECLARE
    checkout TIMESTAMP(3);
    BEGIN
    checkout := '05-MAY-02 12:55:01.272';
    insert into test_table99 (col_is_timestamp) values (checkout);
    END;
    /

    How do I insert into col1 the milliseconds? In other words how do I derive the millie-seconds from SYSDATE?
    Don't blame me, I'm from Red Sox Nation.

  5. #5
    Join Date
    May 2002
    Posts
    2,645
    Actually, you can get the seconds to sssss (five s's).

    SQL> select to_char(sysdate, 'DD-MON-RR HH:MI:SS.SSSSS') from dual;


    TO_CHAR(SYSDATE,'DD-MON-
    ------------------------
    06-JUN-02 01:53:18.49998

  6. #6
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by stecal
    Actually, you can get the seconds to sssss (five s's).

    SQL> select to_char(sysdate, 'DD-MON-RR HH:MI:SS.SSSSS') from dual;


    TO_CHAR(SYSDATE,'DD-MON-
    ------------------------
    06-JUN-02 01:53:18.49998
    'SSSSS' as date format mask simply means seconds elapsed from midnight. It doesn't mean any subseconds, the lowes granule of time with a "clasical" (pre-9i) date type is still one second. You can't get the current time displayed in a subsecond units with Oracle's DATE datatype. No way.

    In the above example, 49998 only shows the number of seconds elapsed from midnight on the current day, which is exactly 01:53:18PM.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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