Date formatiing

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Date formatiing

Date formatiing

Rate Topic

Display Mode

Topic Options

Author

Message

sunder.mekala

Posted Wednesday, February 20, 2013 2:12 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26

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

sturner

Posted Wednesday, February 20, 2013 2:59 PM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 11:56 AM
Points: 1,315, Visits: 2,885

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

sunder.mekala

Posted Wednesday, February 20, 2013 3:09 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26

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

sunder.mekala

Posted Wednesday, February 20, 2013 3:28 PM

Grasshopper

Group: General Forum Members
Last Login: Tuesday, March 05, 2013 10:39 AM
Points: 16, Visits: 26

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

Kingston Dhasian

Posted Thursday, February 21, 2013 12:33 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 6:50 AM
Points: 2,221, Visits: 4,183

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 »

Permissions