-
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...
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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.
Victor
www.dynamicpsp.com
-
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)
10:01:30-56
10:01:30-57
10:01:31-58
...
10:01:31-99
10:01:31-00
...
10:01:31-57
10:01:32-58
Unfortunately there is no easy way you can get SYSDATE and DBMS_UTILITY.GET_TIME in sync.
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|