I am trying to select a date column from database and want to display millesecond with it. How do I do this. I am aware of the "alter session set NLS_DATE_FORMAT = 'MM/DD/YY HH:MI:SS'" command. However, I do not know how to display this with millesecond.
Thanks for all the help.
you cant with sql or pl/sql, you have to create a seperate stored java function or external C procedure
Or upgrade database to 9i and use a new TIMESTAMP datatype...
if you set up nls_Date_format as MM/DD/YY:HH24:MI:SSSS
When ever you select or insert or update oracle will display or store in seconds.
The original poster asked for time portion in miliseconds, seconds are not a problem.... Miliseconds can't be stored/retrieved in/from the database using DATE datatype or any similar built-in datatype until Oracle9i.
You cannot get miliseconds from DATE type,
but you may try to use DBMS_UTILITY.GET_TIME function:
"This function finds out the current time in 100th's of a second. It is primarily useful for determining elapsed time. "
You have to add another column to the table to store value returned by DBMS_UTILITY.GET_TIME function.
Unfortunately DBMS_UTILITY.GET_TIME is not in sync with SYSDATE. That means that when SYSDATE (internal clock of the database) switches to the next higher second, DBMS_UTILITY.GET_TIME does not "overflow" from 99 to 00 100th's of second. So you'll tipicaly get something simmilar when concatenating sysdate and DBMS_UTILITY.GET_TIME:
(hh:mi:ss-cc where cc means 100th's of second from DBMS_UTILITY.GET_TIME)
Unfortunately there is no easy way you can get SYSDATE and DBMS_UTILITY.GET_TIME in sync.