April 12, 2008 at 4:12 am
advance thanks to all...can anybody helpme soon...........
i am using sql server 2005........i want to find out time difference between more than two dates in HH,MM,SS format.
and i want to find out total time difference of all time
eg
first i want the time difference of these two dates
02/Nov/06 9:14:21 AM
19/Apr/07 11:52:31 AM
second i want the time difference of these two dates
02/dec/07 12:14:21 AM
19/jan/08 5:52:31 AM
3rd
i want to find out the total time difference
now i am using this calculation in my procedure is as follows...but i think this is wrong...if we will seperatly calculate like this wrong i am getting
set @totaltravelHr=datediff(HH,@datediff1,@datediff2)
set @totaltravelMI=datediff(MI,@datediff1,@datediff2)
set @totaltravelSE=datediff(SS,@datediff1,@datediff2)
can anybody help me soon??????????
regards
April 14, 2008 at 7:33 am
"datediff(hour, date1, date2)" will give you the hours.
"datediff(minute, date1, date2)" will give you the minutes, but you need to use "datediff(minute, date1, date2)%60" to get the minutes left over after the hours have been calculated. ("%60" gives you the modulus (remainder) after dividing by 60.)
"datediff(second, date1, date2)" will give seconds, but needs to be "(datediff(second, date1, date2)%3600)%60" to give seconds left over after hours and minutes have been calculated.
To get the total difference, I'd run with "datediff(second, date1, date2) + datediff(second, date3, date4)", then divide that by 3600 to get hours, 60 to get minutes.
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 14, 2008 at 8:18 am
Something like this...
declare @d1 datetime
declare @d2 datetime
declare @d3 datetime
declare @d4 datetime
set @d1 = '02/Nov/06 9:14:21 AM'
set @d2 = '19/Apr/07 11:52:31 AM'
set @d3 = '02/dec/07 12:14:21 AM'
set @d4 = '19/jan/08 5:52:31 AM'
declare @t table (Id int, TotalSeconds int)
insert @t
select 1, datediff(second, @d1, @d2)
union select 2, datediff(second, @d3, @d4)
union select 3, datediff(second, @d1, @d2) + datediff(second, @d3, @d4)
select *, cast(Hours as varchar(10)) + substring(convert(varchar(30), dateadd(mi, Minutes, dateadd(s, Seconds, 0)), 109), 15, 6) as 'HH:MM:SS'
from (select Id, TotalSeconds / 3600 as Hours, (TotalSeconds / 60) % 60 as Minutes, TotalSeconds % 60 as Seconds from @t) a
/* Results
Id Hours Minutes Seconds HH:MM:SS
----------- ----------- ----------- ----------- ----------------
1 4034 38 10 4034:38:10
2 1157 38 10 1157:38:10
3 5192 16 20 5192:16:20
*/
Ryan Randall
Solutions are easy. Understanding the problem, now, that's the hard part.
April 14, 2008 at 9:02 am
This is a duplicate post. There's more data in the one that was posted in SQL Server Compact Edition. (In the future, please don't post twice.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 16, 2008 at 11:02 pm
dear friend,
sorry this is a duplicate post...
i posted this same forumn in
http://www.sqlservercentral.com/Forums/Topic484052-323-1.aspx
by mistake i posted two times...
sorry for that...........
any way i solved this problem using simple article...
http://www.sqlteam.com/article/working-with-time-spans-and-durations-in-sql-server
u can check this link...
thanks all
regards
August 28, 2009 at 2:36 pm
Can you write a query and send it to me through which I can get the HH:MM:SS details (time difference) between two date-times (start and end dates)?
I wrote the below query in my server to get the difference between the "Start-Runtime" and "End RunTime"...
select left(DataBaseName,10)"DataBaseName",left(TableName,35)"TableName",
StartRunTime,EndRunTime,
--select
sum(TotalSeconds) / 3600 as Hours,
(sum(TotalSeconds) % 3600) / 60 as Minutes,
sum(TotalSeconds) % 60 as Seconds
from
(
select top 100 DatabaseName, TableName, StartRunTime, EndRunTime,DateDiff(second, StartRunTime, EndRunTime) as TotalSeconds
from uplcore..ObjectQueryTiming
where StartRunTime >= '2009-08-27 20:00:00.000'
order by startruntime desc
) x
group by x.DatabaseName, x.TableName, x.StartRunTime, x.EndRunTime
Thanks.
August 28, 2009 at 3:15 pm
This will work for any datetime range.
select
StartDate,
EndDate,
Hours = datediff(hh,0,DtDiff),
Minutes = datepart(minute,DtDiff),
Seconds = datepart(second,DtDiff)
from
(
select
DtDiff = EndDate-StartDate,
aa.*
from
( -- Test Data
Select
StartDate = convert(datetime,'20090213 02:44:37.923'),
EndDate = convert(datetime,'20090715 13:24:45.837')
) aa
) aResults:
StartDate EndDate Hours Minutes Seconds
----------------------- ----------------------- ----- ------- -------
2009-02-13 02:44:37.923 2009-07-15 13:24:45.837 3658 40 7
(1 row(s) affected)
November 15, 2012 at 11:56 pm
Logic very simple
Find the difference between the two dates in minutes
Then Add the minute to any date starting from time 00 will give the exact result for you
Syntax:
Step 1 : DateDiff(Minute,Date1,Date)
Step 2: DateAdd(minute,Step1,'1-1-1900')
Ex:SELECT dateadd(minute,(datediff(minute,'1-1-1900 09:00','1-1-1900 09:56')),'1-1-1900')
will give you the result 1-1-1900 00:56
You then format the Date time how ever you wanted
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply