DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How do I select date column with millesecond.

  1. #1
    Join Date
    Jul 2000
    Location
    Atlanta
    Posts
    15
    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.

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    you cant with sql or pl/sql, you have to create a seperate stored java function or external C procedure

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  4. #4
    Join Date
    May 2000
    Location
    fremont ca
    Posts
    182
    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.

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  6. #6
    Join Date
    Sep 2001
    Posts
    15
    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

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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
  •  


Click Here to Expand Forum to Full Width