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

Thread: syntax problem

  1. #1
    Join Date
    Nov 2000
    Posts
    56
    Hi,

    In SQL*Plus how do i display both the date and time like
    8-aug-01 04:50:22

    and also how do i find the difference in time like begin_time and end_time when executing a procedure or function?

    Rama
    Rama

  2. #2
    Join Date
    Nov 2000
    Posts
    56
    Syntax like
    select sysdate from dual
    only shows
    08-aug-01

    Rama
    Rama

  3. #3
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    select to_char(sysdate,'dd-mon-yyyy, hh24:mi:ss')
    from sys.dual

    this can be applied to any date column


  4. #4
    I'll answer this in 2 days time

  5. #5
    If you take 2 dats away from each other you are left with the days between them

    i.e.

    Select sysdate - to_date('05-AUG-01') from dual

    is equal to the number of days between the dates, which could be converted into a number of minutes if you wanted.

    Specifically you want the time a procedure started and ended.

    Store the time it started in one variable and the time it finished in another variable. The do the math you require.

    Maybe if timing is set to on it will also tell you how long a procedure took to run.

    [Edited by Zamial on 08-06-2001 at 06:51 AM]

  6. #6
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    date format in Oracle includes both date and time. your problem is only a display one. you can either select to_char(...,'DD/MM/YYYY') or alter your session so that display is changed :
    alter session set NLS_DATE_FORMAT='DD/MM/YYYY HH24:MI:SS'

    for the difference between 2 dates, no problem, you'll get the result as a number in days ...

  7. #7
    Join Date
    Nov 2000
    Posts
    56
    Thank you for the help.
    Now, i want to know how long did the query took to execute. How do i store the begin_time and end_time and find out the difference?

    Rama
    Rama

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Code:
    SQL> select count(*) from dba_users;
    
      COUNT(*)
    ----------
             8
    
    SQL> set timing on
    SQL> select count(*) from dba_users;
    
      COUNT(*)
    ----------
             8
    
    Elapsed: 00:00:00.20
    Jeff Hunter

  9. #9
    Originally posted by Zamial


    Maybe if timing is set to on it will also tell you how long a procedure took to run.
    Beat ya Jeff

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