Click here to Skip to main content
15,868,164 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
if i have StartTime=12:00:00 PM
and EndTime=6:00:00 PM

now i need Time Between this two time ,means i need 3:00:00 PM

how to do this in stored procedure.
Posted
Updated 5-Feb-13 20:28pm
v2

The formula is simple. You add the two numbers and divide them by 2 and you have the mid value.
But the problem here is the numbers here are in time format. Moreover there could be AM/PM issues.
So this is what you do here:
STEP 1: Convert Time1 to milliseconds
STEP 2: Convert Time2 to milliseconds
STEP 3: Result_In_MilliSeconds = (Result_From_Step1 + Result_From_Step2) / 2 
STEP 4: Convert Result_In_MilliSeconds into required time format.


I will leave syntax part for you to do. There is help on Google on how you do these conversions.
 
Share this answer
 
Comments
Member 9511889 6-Feb-13 1:54am    
Is gettime() method is used to convert date to millisecond
Ankur\m/ 6-Feb-13 2:24am    
There is no such in-build function.
Converting into milliseconds is the very simple: ( (hours * 60 * 60) + (mins * 60) + secs ) * 1000
Sandeep Mewara 6-Feb-13 2:25am    
My 5!
Ankur\m/ 6-Feb-13 2:30am    
Thanks!
Renju Vinod 6-Feb-13 3:38am    
+5
This is the query .

SELECT DATEADD(ms,DATEDIFF(ms,'2012-10-04 12:48:56:000', '2012-10-04 12:48:58:000')/2,'2012-10-04 12:48:56:000')
 
Share this answer
 
Read about TimeSpan. You need to find the difference between the logout time and the login time. Using Timespan, you get the difference in terms of hours/minutes/seconds, etc

Steps here:
1. Get the timespan(ts) between from & to date.
2. Add half of ts to from date OR substract half of ts from to date.

Refer:
MSDN: TimeSpan Structure[^]
DateTime.Subtract Method (TimeSpan)[^]

Above solution is to do on server side code. Logic remains same for database query too. Convert it into a standard format and then get the difference and then add half the difference to initial date.
 
Share this answer
 
v2
Comments
Ankur\m/ 6-Feb-13 2:20am    
He needs it to be done on SQL Server (mentioned in the question).
Sandeep Mewara 6-Feb-13 2:24am    
:doh: I went with the tags.
Ankur\m/ 6-Feb-13 2:28am    
Yeah they were misleading. I have updated it to 'database' as actual database used is not know.
Sandeep Mewara 6-Feb-13 2:30am    
:thumbsup:

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