Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Date formatiing Expand / Collapse
Author
Message
Posted Wednesday, February 20, 2013 2:12 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:38 PM
Points: 16, Visits: 54
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.
Post #1422299
Posted Wednesday, February 20, 2013 2:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, August 21, 2014 1:54 PM
Points: 1,432, Visits: 3,229
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.
Post #1422328
Posted Wednesday, February 20, 2013 3:09 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:38 PM
Points: 16, Visits: 54
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.
Post #1422330
Posted Wednesday, February 20, 2013 3:28 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 13, 2014 1:38 PM
Points: 16, Visits: 54
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.
Post #1422336
Posted Thursday, February 21, 2013 12:33 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 22, 2014 12:13 AM
Points: 2,631, Visits: 4,723
Even this would do the work for you and is probably much simpler

SELECT	CONVERT(TIME, DATEADD(SECOND, DATEDIFF(SECOND, minibgtime, maxendtime), '' ) )
FROM tablename




Kingston Dhasian

How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1422419
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse