Calculate HH:MM:SS between two times -- sql server reporting services

Michael C
5/17/2007 3:48:02 PM

John,

Try looking up the DateDiff() function. You may have to get an answer in
minutes (or seconds) and convert it to the format you want though.

Michael

[quoted text, click to view]

Lisa Slater Nicholls
5/23/2007 11:09:11 PM

Here's a q&d version that should do what you want .. including the right
padding. It looks silly but the trick is subtracting the higher-level
elements to get the right numbers in the smaller units.

There is probably a way to do this with modulus (% operator) rather than the
subtractions but I'm too tired to think of it <g>.

SELECT RIGHT('00' + CAST(DATEDIFF(hour,<date1>,<date2>) AS VARCHAR(2)),2) +
':' +
RIGHT('00'+ CAST(DATEDIFF(minute,<date1>,<date2>) -
DATEDIFF(hour,<date1>,<date2>)* 60 AS
VARCHAR(2)),2) +
':' +
RIGHT('00' + CAST(DATEDIFF(second,<date1>,<date2>) -
(DATEDIFF(hour,<date1>,<date2>)* 3600 +
DATEDIFF(minute,<date1>,<date2>) * 60) AS
VARCHAR(2)),2)

[quoted text, click to view]

[quoted text, click to view]

Lisa Slater Nicholls
5/23/2007 11:28:22 PM

Whoops. Got a little carried away there with the subtractions there.. told
you I was tired <g>...

I think this is right:

SELECT RIGHT('00' + CAST(DATEDIFF(hour,<date1>,<date2>) AS VARCHAR(2)),2) +
':' +
RIGHT('00'+ CAST(DATEDIFF(minute,<date1>,<date2>) -
DATEDIFF(hour,<date1>,<date2>)* 60 AS
VARCHAR(2)),2) +
':' +
RIGHT('00' + CAST(DATEDIFF(second,<date1>,<date2>)-
(DATEDIFF(minute,<date1>,<date2>) * 60) AS
VARCHAR(2)),2)

.... and, again, there has got to be an easier way...

[quoted text, click to view]

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Calculate HH:MM:SS between two times