interval dates

i want to create a user-defined function that accepts 2 dates and returns
the time difference in days, hours, minutes, and seconds-
for example, a possible result would be something like:
2 days, 17 hours, 46 minutes, 12 seconds
has anyone out there done this before in sql server? any help would be much
appreciated.
thanks,
jtjtl
CREATE FUNCTION dbo.dates_range
(
@.date1 DATETIME,
@.date2 DATETIME
)
RETURNS VARCHAR(32)
AS
BEGIN
DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
SET @.sR = @.sD % 60
SET @.mD = (@.sD - @.sR) / 60
SET @.mR = @.mD % 60
SET @.hR = (@.mD - @.mR) / 60
RETURN CONVERT(VARCHAR, @.hR)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
+':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
END
"JTL" <jliautaud@.hotmail.com> wrote in message
news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>i want to create a user-defined function that accepts 2 dates and returns
>the time difference in days, hours, minutes, and seconds-
> for example, a possible result would be something like:
> 2 days, 17 hours, 46 minutes, 12 seconds
> has anyone out there done this before in sql server? any help would be
> much appreciated.
> thanks,
> jt
>|||thanks!
do you know how i can return days as well?
jtl
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
> jtl
> CREATE FUNCTION dbo.dates_range
> (
> @.date1 DATETIME,
> @.date2 DATETIME
> )
> RETURNS VARCHAR(32)
> AS
> BEGIN
> DECLARE @.sD INT, @.sR INT, @.mD INT, @.mR INT, @.hR INT
> SET @.sD = DATEDIFF(SECOND, @.date1, @.date2)
> SET @.sR = @.sD % 60
> SET @.mD = (@.sD - @.sR) / 60
> SET @.mR = @.mD % 60
> SET @.hR = (@.mD - @.mR) / 60
> RETURN CONVERT(VARCHAR, @.hR)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.mR), 2)
> +':'+RIGHT('00'+CONVERT(VARCHAR, @.sR), 2)
> END
>
>
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:uo90dQFDGHA.2908@.TK2MSFTNGP09.phx.gbl...
>|||You could do something like this with my TTimeSpan UDT at [0]:
declare @.ts TTimeSpan
set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
select @.ts.ToString(), @.ts.ToLongString()
Output:
-- ---
11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
[0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
William Stacey [MVP]
"JTL" <jliautaud@.hotmail.com> wrote in message
news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
> thanks!
> do you know how i can return days as well?
> jtl
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23rZKdUFDGHA.1312@.TK2MSFTNGP09.phx.gbl...
>|||i didn't see where to get the source for TTimeSpan- can you help?
jt
"William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
> You could do something like this with my TTimeSpan UDT at [0]:
> declare @.ts TTimeSpan
> set @.ts = TTimeSpan::FromDates('1/1/2005 2:00', '1/12/2005 14:6')
> select @.ts.ToString(), @.ts.ToLongString()
> Output:
> -- ---
--
> 11.12:6:0 11 Days 12 Hours 6 Minutes 0 Seconds 0 Milliseconds
> [0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390
> --
> William Stacey [MVP]
> "JTL" <jliautaud@.hotmail.com> wrote in message
> news:%23APwXiFDGHA.2700@.TK2MSFTNGP14.phx.gbl...
>|||JTL (jliautaud@.hotmail.com) writes:
> i didn't see where to get the source for TTimeSpan- can you help?
What about reading William's post in full?
> "William Stacey [MVP]" <william.stacey@.gmail.com> wrote in message
> news:uctoCXQDGHA.2436@.TK2MSFTNGP15.phx.gbl...
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx