syntax problem

[yumi_rama]

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

[yumi_rama]

Syntax like
select sysdate from dual
only shows
08-aug-01

Rama

[Sureshy]

select to_char(sysdate,'dd-mon-yyyy, hh24:mi:ss')
from sys.dual

this can be applied to any date column

[Zamial]

I'll answer this in 2 days time

[Zamial]

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]

[pipo]

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 ...

[yumi_rama]

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

[marist89]

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

[Zamial]

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