Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Time difference in

A1 =17/07/2014 12:47:46 PM -date
A2 =12:10-Varchar
A3 =17/07/2014 10:53:34 AM -date

1)a1-a2(hours,min) = 0hrs37mins
2)a1-a3(hours,min) = 1hrs54mins

thanks

What I have tried:

(TO_DATE(to_CHAR(a1,'hh24:mi'),'HH24:MI') - TO_DATE(to_CHAR(a2,'hh24:mi'),'HH24:MI'))*24 as OBhour,
(TO_DATE(to_CHAR(a1,'hh24:mi'),'HH24:MI') - TO_DATE(to_CHAR(a2,'hh24:mi'),'HH24:MI'))* 24 * 60 as OBmin

----output ----
for
1)0hrs 37mins
2)1.54hrs 114mins
Posted
Updated 19-Apr-18 23:37pm
v2
Comments
Jochen Arndt 20-Apr-18 5:13am    
And what is the problem / question?
Parazival 20-Apr-18 5:34am    
thanks for replay
when i try above mentioned query its showing for like
1)0hrs 37mins
2)1.54hrs 114mins
Jochen Arndt 20-Apr-18 5:57am    
Please use the Reply button right of a comment to answer. Than an email notification is send.

Now I got it. You want to format the output as hh24:mi.
You can use the function DATEDIFF to get the difference in seconds.
Then pass the result to
TO_CHAR(TO_DATE(seconds,'sssss'),'hh24:mi')
Or use mathematical operations to calculate the hours and minutes and format those with TO_CHAR:
hours = TRUNC(seconds/3600)
minutes = TRUNC(MOD(seconds,3600)/60)

1 solution

See TO_CHAR (datetime)[^] for proper date conversions. Also, you should be using DateTime types only for all stored values and calculations. The only time you need to convert a DateTime to a character string is when you need to display it.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900