SQL for Smarties | SQL Programming Style | Trees and Hierarchies in SQL | SQL Puzzles and Answers | Data and Databases

Monday, May 01, 2006

Calculating Hours, Mins over 24 hour periods

SQL Apprentice Question
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

RESULTS **********

[decDuration] [realDuration] [realDuration2]
29.145000 29:8:42 05:08:42

Celko Answers
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?

No comments: