DATE type finer than seconds

[gopi]

(9i, Solaris)
Can I get a date more refined than the second?

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

If not, anything creative?

[jamoji]

Select dbms_utility.get_time from dual;

It shows the time in miliseconds...

[pando]

there are new data types that supports milliseconds, look for timestamp datatypes

[gopi]

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?

[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

[jmodic]

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.