February 20, 2013 at 2:12 pm
Hi,
Here minibgtime and maxendtime are in below format
2013-02-19 16:15:48.000
select
datediff (Mi,Cast((minibgtime) as datetime), Cast((maxendtime) as datetime))/60) as Total_Minutes
from tablename
Now i am getting something like a number 150 minutes but i want to see that as 2:30:00
as 150 mins = two hrs 30 minutes.
Any help is appreciated.
Thanks,
Sam.
February 20, 2013 at 2:59 pm
add those minutes to a date @ 00:00:00 hrs then format as a time. As an example:
declare @mins int
set @mins = 150
select convert(char(5),dateadd(minute,@mins,'01/01/2013'),108)
The probability of survival is inversely proportional to the angle of arrival.
February 20, 2013 at 3:09 pm
select
datediff (Mi,Cast((minibgtime) as datetime), Cast((maxendtime) as datetime))/60) as Total_Minutes
from tablename
The above Sql Query should give me 2:30:00 instead of 150 minutes, here i have make changes in the above query , just to clarify i have to make changes inthe above query itself.
Thankyou.
--
Sam.
February 20, 2013 at 3:28 pm
Never Mind i figured out
select
(CONVERT(varchar(6), DATEDIFF(second, minibgtime, maxendtime)/3600) + ':' +
RIGHT('0' + CONVERT(varchar(2), (DATEDIFF(second, minibgtime, maxendtime) % 3600) / 60), 2) + ':' +
RIGHT('0' + CONVERT(varchar(2), DATEDIFF(second, minibgtime, maxendtime) % 60), 2)) as Total_Time
from tablename
Thankyou.
--
Sam.
February 21, 2013 at 12:33 am
Even this would do the work for you and is probably much simpler
SELECTCONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, minibgtime, maxendtime), '' ) )
FROMtablename
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply