time calculation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

time calculation

Author

Topic

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-01-04 : 02:11:01

Hi,
I need to compute the total interval time for this sample:

Records

In | Out
8:30AM | 4:45PM
1:00pm | 6:10pm
2:00pm | 2:15pm

i want to have a result of
8:15:00
5:10:00
00:15:00 mins
thnk you for your help

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-04 : 02:15:43

what are the datatypes of the in/out columns?

elsasoft.org

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-01-04 : 02:30:10

datetime

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2008-01-04 : 02:40:39

select datediff(minute, [in], [out]) from yourtable

btw, in and out are very poor choices of column names. "in" is a reserved word in sql.

elsasoft.org

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-01-04 : 04:56:51

select datediff(minute, [in], [out]) from yourtable --- this will only give the results in minutes. if it s more than 60minutes how can i convert it to hours

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2008-01-04 : 05:11:56

select RIGHT('00' + LTRIM(STR(datediff(second, [in], [out]) / 3600, 2)), 2) + ':' + RIGHT('00' + LTRIM(STR((datediff(second, [in], [out]) - ((datediff(second, [in], [out]) / 3600) * 3600)) / 60, 2)), 2) as Mins from yourtable

Duane.

georgev
Posting Yak Master

122 Posts

Posted - 2008-01-04 : 06:00:51

quote:
Originally posted by sign_seventh

select datediff(minute, [in], [out]) from yourtable --- this will only give the results in minutes. if it s more than 60minutes how can i convert it to hours

You do it in the presentation layer.

George
<3Engaged!

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-04 : 08:13:25

quote:
Originally posted by georgev

quote:
Originally posted by sign_seventh

select datediff(minute, [in], [out]) from yourtable --- this will only give the results in minutes. if it s more than 60minutes how can i convert it to hours

You do it in the presentation layer.

George
<3Engaged!

I see my echo

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-01-04 : 11:57:15

had a couple minutes to kill before a meeting:

DECLARE @T TABLE (InDate DATETIME, OutDate DATETIME)

INSERT @T
SELECT '8:30 AM', '4:45 PM'
UNION ALL SELECT '1:00pm', '6:10pm'
UNION ALL SELECT '2:00pm', '2:15pm'

SELECT 
	CASE
		WHEN DATEPART(HOUR, DATEADD(SECOND, DATEDIFF(SECOND, InDate, OutDate), 0)) = 0
			THEN CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, InDate, OutDate), 0), 108) + ' mins'
		ELSE CONVERT(VARCHAR(8), DATEADD(SECOND, DATEDIFF(SECOND, InDate, OutDate), 0), 108)
	END		
FROM @T

sign_seventh
Yak Posting Veteran

66 Posts

Posted - 2008-01-08 : 21:42:53

thx so much for the help, i did another tricks.
cast((DATEDIFF(minute, TimeIn, TimeOut)/60)as varchar(50))+':'+
cast((DATEDIFF(minute, TimeIn, TimeOut)%60) as varchar(50))+':00' as Duration

thnx again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources