DateDiff problem - 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 2000 Forums

Transact-SQL (2000)

DateDiff problem

Author

Topic

Gili
Starting Member

42 Posts

Posted - 2008-01-28 : 09:28:52

Hi,

I have to make a minute calc whice return the diff between two datetime values and the calculation have to be only on the time part of the datetime value.

The Result i get now:

StartTime EndTime Result Of DateDiff
11:49PM 12:48AM 1381

The result i want to see:

StartTime EndTime Result Of DateDiff
11:49PM 12:48AM 59

this is what i've did:
abs( datediff(minute, right(a.StartTIME,7),right(b.EndTime,7)))

any ideas??
Tanks..

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 09:47:44

[code]-- Prepare sample data
DECLARE @Sample TABLE (dt1 DATETIME, dt2 DATETIME)

INSERT @Sample
SELECT '11:49PM', '12:48AM' UNION ALL
SELECT '12:48AM', '11:49PM'

-- Show the expected output
SELECT d1,
d2,
DATEDIFF(MINUTE, d1, d2) % 1440 AS MinuteDiff
FROM (
SELECT CASE
WHEN dt1 > dt2 THEN dt1
ELSE dt2
END AS d1,
CASE
WHEN dt1 > dt2 THEN DATEADD(DAY, 1, dt2)
ELSE DATEADD(DAY, 1, dt1)
END AS d2
FROM @Sample
) AS z[/code]

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-01-28 : 09:50:18

declare @start varchar(10), @end varchar(10)
select @start='11:49PM',@end ='12:48AM'
select datediff(minute,cast(@start as datetime),cast(@end as datetime)+1)

Madhivanan

Failing to plan is Planning to fail

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-28 : 09:54:00

Simpler and accurate (now also handles times within same day where fromtime is less than totime) than 01/28/2008 : 09:47:44...

-- Prepare sample data
DECLARE	@Sample TABLE (dt1 DATETIME, dt2 DATETIME)

INSERT	@Sample
SELECT	'11:49PM', '12:48AM' UNION ALL
SELECT	'12:48AM', '11:49PM'

-- Show the expected output
SELECT	dt1,
	dt2,
	(1440 + DATEDIFF(MINUTE, dt1, dt2)) % 1440
FROM	@Sample

dt1	dt2	(No column name)
23:49	00:48	59
00:48	23:49	1381

E 12°55'05.25"
N 56°04'39.16"

Gili
Starting Member

42 Posts

Posted - 2008-01-28 : 10:07:50

Thanks everyone!!!
:-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources