my requirement is :
Time1 :=' 09:12 '
Time2 :=' 18:24'
now kind give me a query to find difference between time1 and time2 in
hour minute and second form.
for ex (2 hrs 35 mins 45 sec)
Thanx in advance
Printable View
my requirement is :
Time1 :=' 09:12 '
Time2 :=' 18:24'
now kind give me a query to find difference between time1 and time2 in
hour minute and second form.
for ex (2 hrs 35 mins 45 sec)
Thanx in advance
I'm not sure you can do this 'nicely' in one query.
Something like this:
SELECT (SYSDATE - (SYSDATE - 0.1)) * 24*60*60
FROM dual;
will get you the interval in seconds. Then something like this:
SELECT TO_CHAR(TRUNC(:time_in_seconds/60/60),'09') ||'hrs'||
TO_CHAR(TRUNC(MOD(:time_in_seconds,3600)/60),'09') ||'mins'||
TO_CHAR(MOD(MOD(:time_in_seconds,3600),60),'09')||'secs'
FROM dual;
...will format. You might consider writing a function for this.
J
I have attached one file.
This function will return hours and minutes. e.g. 22:30. You have to modify it as per your requirement.
bhattnirav offers an excellent solution.
This achieves the same, but in a different way.
CREATE OR REPLACE FUNCTION j_get_time_interval (p_start_time IN DATE, p_end_time IN DATE)
RETURN VARCHAR2 IS
nTimeIntervalInSeconds NUMBER;
BEGIN
SELECT (p_end_time - p_start_time) * 24*60*60
INTO nTimeIntervalInSeconds
FROM dual;
RETURN TO_CHAR(TRUNC(nTimeIntervalInSeconds/60/60),'09') ||'hrs'||
TO_CHAR(TRUNC(MOD(nTimeIntervalInSeconds,3600)/60),'09') ||'mins'||
TO_CHAR(MOD(MOD(nTimeIntervalInSeconds,3600),60),'09')||'secs';
END j_get_time_interval;
/
Hi Binoy,
This will also achieves the same, but in a single query.
SELECT TO_CHAR(TRUNC((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60)/60/60),'09') ||':'||
TO_CHAR(TRUNC(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600)/60),'09') ||':'||
TO_CHAR(MOD(MOD((( to_date(to_char(sysdate,'DD-MON_YYYY')||' 10:30:00','DD-MON_YYYY HH24:MI:SS' ) -
to_date(to_char(sysdate,'DD-MON_YYYY')||' 09:45:00','DD-MON_YYYY HH24:MI:SS' ) ) * 24*60*60),3600),60),'09') as "HH:MI:SS"
FROM dual;