"JTL" <jliaut***@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
>

"Uri Dimant" <u***@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" <jliaut***@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
>>
>
>

"JTL" <jliaut***@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" <u***@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" <jliaut***@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
>>>
>>
>>
>
>

"William Stacey [MVP]" <william.sta***@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" <jliaut***@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" <u***@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" <jliaut***@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
>>>>
>>>
>>>
>>
>>
>
>

> "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message
> news:uctoCXQDGHA.2436@TK2MSFTNGP15.phx.gbl...
>> You could do something like this with my TTimeSpan UDT at [0]:
>>....
>> [0] - http://channel9.msdn.com/ShowPost.aspx?PostID=147390