Calculating Hours, Mins over 24 hour periods

Below I'm returning the decimal duration [decDuration], a HH:MM:SS format
without "padding" 0's [realDuration], and a HH:MM:SS format with "padding"
0's [realDuration2].
If you run my EXAMPLE, in RESULTS below you'll see that [decDuration] and
[realDuration] work fine, but [realDuration2] looses 24 hours if a duration
spans over a day.
Can someone help me modify my formula for [realDuration2] so it doesn't
loose 24 hours if a duration is longer than a day?
EXAMPLE **********
declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 09:34:11'
set @.dtEndDate = '20060315 14:42:53'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select CONVERT(decimal(10, 6), @.duration / 3600.0) AS decDuration,
RTRIM(@.duration/3600) + ':' + RTRIM(@.duration % 3600/60) + ':' +
RTRIM(@.duration % 60) AS realDuration,
CONVERT(varchar,CONVERT(datetime,DATEADD
(s,@.duration,'19000101' )),108) AS
realDuration2
RESULTS **********
[decDuration] [realDuration] [realDuration2]
----
29.145000 29:8:42 05:08:42declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
set @.dtStartDate = '20060314 09:34:11'
set @.dtEndDate = '20060315 14:42:53'
set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
select
CONVERT(decimal(10, 6), @.duration / 3600.0)
AS decDuration,
RTRIM(@.duration/3600) + ':'
+ RTRIM(@.duration % 3600/60) + ':'
+ RTRIM(@.duration % 60)
AS realDuration,
RIGHT('00'+RTRIM(@.duration/3600),2) + ':'
+ RIGHT('00'+RTRIM(@.duration % 3600/60),2) + ':'
+ RIGHT('00'+RTRIM(@.duration % 60),2)
AS realDuration2
"scott" <sbailey@.mileslumber.com> wrote in message
news:u0pS4rHbGHA.3328@.TK2MSFTNGP02.phx.gbl...
> Below I'm returning the decimal duration [decDuration], a HH:MM:SS format
> without "padding" 0's [realDuration], and a HH:MM:SS format with "padding"
> 0's [realDuration2].
> If you run my EXAMPLE, in RESULTS below you'll see that [decDuration] and
> [realDuration] work fine, but [realDuration2] looses 24 hours if a
> duration spans over a day.
> Can someone help me modify my formula for [realDuration2] so it doesn't
> loose 24 hours if a duration is longer than a day?
> EXAMPLE **********
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 09:34:11'
> set @.dtEndDate = '20060315 14:42:53'
> set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
> select CONVERT(decimal(10, 6), @.duration / 3600.0) AS decDuration,
> RTRIM(@.duration/3600) + ':' + RTRIM(@.duration % 3600/60) + ':' +
> RTRIM(@.duration % 60) AS realDuration,
> CONVERT(varchar,CONVERT(datetime,DATEADD
(s,@.duration,'19000101' )),108) AS
> realDuration2
>
> RESULTS **********
> [decDuration] [realDuration] [realDuration2]
> ----
> 29.145000 29:8:42 05:08:42
>|||that did it, thanks.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:%23p9PjrIbGHA.4676@.TK2MSFTNGP04.phx.gbl...
> declare @.dtStartDate datetime, @.dtEndDate datetime, @.duration as int
> set @.dtStartDate = '20060314 09:34:11'
> set @.dtEndDate = '20060315 14:42:53'
> set @.duration = datediff(s,@.dtStartDate,@.dtEndDate)
> select
> CONVERT(decimal(10, 6), @.duration / 3600.0)
> AS decDuration,
> RTRIM(@.duration/3600) + ':'
> + RTRIM(@.duration % 3600/60) + ':'
> + RTRIM(@.duration % 60)
> AS realDuration,
> RIGHT('00'+RTRIM(@.duration/3600),2) + ':'
> + RIGHT('00'+RTRIM(@.duration % 3600/60),2) + ':'
> + RIGHT('00'+RTRIM(@.duration % 60),2)
> AS realDuration2
>
>
> "scott" <sbailey@.mileslumber.com> wrote in message
> news:u0pS4rHbGHA.3328@.TK2MSFTNGP02.phx.gbl...
>|||Why not keep track of total minutes (or seconds, if appropriate) and
let the front end worry about the display format? Tiered architecture
and all that jazz?