Subtracting Dates - 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

SQL Server Development (2000)

Subtracting Dates

Author

Topic

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-11-18 : 21:44:28

I am looking to subtract dates/times and end up with hours, min, sec. My code looks somthing like this but of course I end up with off years and times. The code would have to condsider dates just in case the subtraction took place around a new year. But I simply want to end up with time differences. Any thoughts on this one?

SELECT ((CONVERT(datetime, SDS_Start_time))-(CONVERT(datetime, SDS_Cont_time))),
((CONVERT(datetime, SDS_Cont_time))- (CONVERT(datetime, SDS_Complete_time))),
((CONVERT(datetime, SDS_Complete_time))- time_date),
(time_date - (CONVERT(datetime, On_dry_weight))),
((CONVERT(datetime, SDS_Start_time))- (CONVERT(datetime, On_dry_weight)))
FROM no3pm_startup

Here were the times I started with. Note: I had to convert most of them except time_date:

SDS_Start_time = 11/18/02 3:07:38 PM
SDS_Cont_time = 11/18/02 3:15:24 PM
SDS_Complete_time = 11/18/02 3:20:33 PM
On_dry_weight = 11/18/02 3:48:33 PM
time_date = 11/18/2002 3:36:08 PM

Here are my results:
1899-12-31 23:52:14.000
1899-12-31 23:54:51.000
1899-12-31 23:44:25.450
1899-12-31 23:47:34.550
1899-12-31 23:19:05.000

Here is my desired result:
0:07:46
0:05:09
0:15:35
0:12:25
0:40:55

Thanks in advance.

John

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-18 : 23:32:20

create a function to return datediff as a time

CREATE FUNCTION dbo.fx_DateDiffAsTime
(@sdate datetime, @fdate datetime)

RETURNS nvarchar(50)
AS
BEGIN
declare @diff float
declare @sec int
declare @min int
declare @hour int
declare @out nvarchar(50)

--get hours and minutes and seconds
set @diff = datediff(ss, @sdate, @fdate)
set @hour = floor(@diff / 3600)
set @diff = @diff - (@hour * 3600)
set @min = floor(@diff / 60)
set @sec = @diff - (@min * 60)

--and output
set @out = cast(@hour as nvarchar(40)) + substring(convert(nvarchar(50), convert(datetime, '00:' + cast(@min as char(2)) + ':' + cast(@sec as char(2))), 108),3,6)

return @out
END

then you can just do

select dbo.fx_DateDiffAsTime(@sdate, @fdate)

HTH

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-18 : 23:37:06

and if it is a homework assignment, let me know what mark you get!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-11-19 : 08:16:15

Its not homework. I'm an engineer for a large paper manufacturing outfit. I normally do controls work but lately I have been sending a lot of our data to SQL and munipulating it.

Thanks for your help and your humor.... I think.

John

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-19 : 17:12:34

OK - apologies - must just be that "young at heart" thing you've got going!

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

rrb
SQLTeam Poet Laureate

1479 Posts

Posted - 2002-11-19 : 19:03:39

PS - how did you go?

--
I hope that when I die someone will say of me "That guy sure owed me a lot of money"

jpiscit1
Posting Yak Master

130 Posts

Posted - 2002-11-25 : 09:22:41

My final ended up something like this:

DECLARE @hours_dif Int, @minutes_dif Int
Declare @diff_total int
set @diff_total = (Select TOP 1 DateDiff (mi, On_dry_weight, SDS_Start_time) FROM no3pm_startup WHERE (no3pm_startup.product = 'PF-4N') AND (no3pm_startup.caliper = '0.016') ORDER BY time_date DESC)

SELECT @hours_dif = @diff_total / 60
SELECT @minutes_dif = @diff_total % 60

SELECT CONVERT(Char(5), @hours_dif) + ':' + CONVERT (Char(2), @minutes_dif)

Thanks again for all your help!

This worked very well.

John

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources