Total Hours

Last Post 24 Oct 2007 05:28 AM by SQLUSA. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
sgfubt321
New Member
New Member

--
23 Oct 2007 07:47 AM
We have data that shows x, y hours. It is in the following format (varchar):

x y
03:30 AM MID
09:30 AM 05:30 PM
MID 01:00 PM
NOON 08:00 PM

When I try and change the MID to 00:00 and use the datediff(hh, x, y)
I do not get the desired results. For example:

select datediff(hh, '12:00','20:00') this is correct
select datediff(hh, '15:30','00:00') give me -15 should be 8
select datediff(hh, '09:30','17:30') this is correct

What am I doing wrong? What should the MID be listed as and the NOON to work correctly with the other times? Thank you for any assistance in advance...
SQLUSA
New Member
New Member

--
23 Oct 2007 10:48 AM
set 00:00 to 23:59


Kalman Toth
SQL Server Training - http://www.sqlusa.com
nosepicker
New Member
New Member

--
23 Oct 2007 11:31 AM
Just so you know, when you pass in the time elements to the datediff function in this way, SQL Server is making an implicit conversion of those times to datetime format, using Jan. 1, 1900 as the date. To see this, do this:

SELECT CONVERT(datetime, '15:30')
(Result: 1900-01-01 15:30:00.000)

So you can see that if you pass '00:00' into the datediff function, it gets converted to '1900-01-01 00:00:00.000', which is why you got the negative number.

If you only need to determine differences in hours, then setting 00:00 to 23:59 would be ok, provided you make sure that 00:00 is the second time element instead of the first. But using 23:59 would of course be inaccurate if you are trying to determine differences in minutes.

sgfubt321
New Member
New Member

--
24 Oct 2007 04:53 AM
Thanks for the useful tips..It just so happens I do have entries that look similiar to the following:

x y
23:30 00:15 should be 00:45 minutes - but when I try and get the minutes I get -1395??

What to do here? Thank you again.
SQLUSA
New Member
New Member

--
24 Oct 2007 05:28 AM
You would have to do it in 2 segments and combine:

23:59:59 (ending) (starting) 00:00:01
SwePeso
New Member
New Member

--
24 Oct 2007 11:39 PM
Since you are only storing the time information, there is no guarantee that the times actually are same date or sequential dates.

DECLARE @Sample TABLE (dt1 DATETIME, dt2 DATETIME)

INSERT @Sample
SELECT '12:00', '20:00' UNION ALL
SELECT '15:30', '00:00' UNION ALL
SELECT '09:30', '17:30'

SELECT dt1,
dt2,
DATEDIFF(MINUTE, dt1, CASE WHEN dt2 < dt1 THEN 1 + dt2 ELSE dt2 END) / 60.0
FROM @Sample
You are not authorized to post a reply.

Acceptable Use Policy