Time Diff Between 2 Columns 1

[AndyFreeman]

Hi there

I have 2 date/time fields taht i wish to know the diff between.
I am looking for the SQL that will show me the diff between

Time_Arrival and Time_Left

The times are stored as 1900-01-01 10:39:00 and 1900-01-01 10:39:00

Am guessing i need to to_date them or something but am relatively new to this

Thanks in advance

Andy

 

[AndyFreeman]

request_event_eta.EVENT_TM - request_event_arrv.EVENT_TM time_diff

I have this working to a point but am getting results of this "-0.047118055555556"

Can i convert this value in any way to be a Hours format

 

[HimanB]

Hi,
Make use of following query:

:     select floor((date1-date2)*24*60*60)/3600)||'HOURS'||floor((((date1-date2)*24*60*60) - 
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)||'MINUTES'||round((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600 -(floor((((date1-date2)*24*60*60) -
       floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))||'SECS' timediff  from Your_table;

HTH
Regards
Himanshu

 

[AndyFreeman]

select request.created_tm as date1, request.last_updated_tm as date2 floor((date1-date2)*24*60*60)/3600)||'HOURS'||floor((((date1-date2)*24*60*60) - floor(((date1-date2)*24*60*60)/3600)*3600)/60)||'MINUTES'||round((((date1-date2)*24*60*60) -floor(((date1-date2)*24*60*60)/3600)*3600 -(floor((((date1-date2)*24*60*60) -floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))||'SECS' timediff FROM request;

Tried your suggestion but am getting this message back, where am i going wrong?

<eb1>ORA-00923: FROM keyword not found where expected
State:S1000,Native:923,Origin:[Oracle][ODBC][Ora]</eb1>

 

[nadeu26]

there's one left parethesis missing at first line
select floor ( ((date1-date2)*24*60*60)/3600)||'HOURS'||floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)||'MINUTES'||round((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600 -(floor((((date1-date2)*24*60*60) -
floor(((date1-date2)*24*60*60)/3600)*3600)/60)*60)))||'SECS' timediff from Your_table;