Calculate HH:MM:SS between two times

Hi,
I'm trying to get a result of HH:MM:SS that occurs between two times. I'm
not sure exactly how to do this, or if I should do this in SQL or as an
expression in a table. As an example:
11:01:00 PM to 12:02:00 AM= 01:01:00
Thanks for an help
--
JohnJohn,
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
"John" wrote:
> Hi,
> I'm trying to get a result of HH:MM:SS that occurs between two times. I'm
> not sure exactly how to do this, or if I should do this in SQL or as an
> expression in a table. As an example:
> 11:01:00 PM to 12:02:00 AM= 01:01:00
> Thanks for an help
> --
> John|||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)
>L<
"John" <John@.discussions.microsoft.com> wrote in message
news:6A643034-CF90-4195-9BBF-13C63BF2512F@.microsoft.com...
> Hi,
> I'm trying to get a result of HH:MM:SS that occurs between two times. I'm
> not sure exactly how to do this, or if I should do this in SQL or as an
> expression in a table. As an example:
> 11:01:00 PM to 12:02:00 AM= 01:01:00
> Thanks for an help
> --
> John|||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...
>L<
"John" <John@.discussions.microsoft.com> wrote in message
news:6A643034-CF90-4195-9BBF-13C63BF2512F@.microsoft.com...
> Hi,
> I'm trying to get a result of HH:MM:SS that occurs between two times. I'm
> not sure exactly how to do this, or if I should do this in SQL or as an
> expression in a table. As an example:
> 11:01:00 PM to 12:02:00 AM= 01:01:00
> Thanks for an help
> --
> John