Adding Time In DateTime Field

Hi

I'm trying to add a time from a DateTime field to provide a total. Eg:

Field1

01/02/2007 01:00:00PM

01/03/2007 01:45:00PM

01/04/2007 03:00:00PM

I want to add the time so I get a total of 05:45. The total hours could go over 24. I know I can't Sum it. I've seen several examples of how to do this but can't make any of them work. Could someone please point me in the right direction?

Thanks

set @.d1='01/02/2007 01:00:00PM'

datepart(hh,@.d1) return : 1

DATEADD ( hour, datepart(hh,@.d1), YourDate ) add 1 hour to your data

|||

Please check your objective. All of the times you list are afternoon times. The sum of the time component for all of these is the 5:45 plus an additional 36 hours. If your answer is correct, there is more to it than just summing the time components.

Code Snippet

declare @.aTable table (field1 datetime)
insert into @.aTable
select '01/02/2007 01:00:00PM' union all
select '01/03/2007 01:45:00PM' union all
select '01/04/2007 03:00:00PM'

select datediff(day, 0, sumOfTime) as Days,
convert(varchar, sumOfTime, 114) as Time
from ( select cast(sum(cast(field1 as float)
-floor(cast(field1 as float)))as datetime)
as sumOfTime
from @.aTable
) x

/*
Days Time
--
1 17:44:59:997
*/

|||

From the nature of your question, and looking at your sample data, I assume that the sample data represents 'elapsed time' on a date. And that 01:45PM means 1 hour and 45 minutes elapsed time -NOT 13:45 o'clock.

To calculate the total 'elapsed time', it would have been so much easier if you were storing the StartDateTime and EndDateTime -then it would be relatively simple date arithematic.

If my assumptions are correct, AND you cannot re-engineer the data to collect Start/End datetime values, this will be a bit more effort.

Please confirm.

|||

Sorry, I should have been clearer. The time is just a time, the date is irrelevant. It's actually a travel time, so Arnie you're correct, it is an elapsed time. I'm purely interested in adding the hours together. So in the 3 lines of sample data they travelled for 1 hour, 1 hour 45 minutes and 3 hours. AM/PM is also irrelevant. Travel time will never go over 12 hours. So the total I want is 5:45.

I'm working with someone else's data and tables here, personally I wouldn't have used a datetime field for this data but that is what I have. I also agree it would be better to have a start and end time, but I don't.

One possible way could be to extract the time, convert it to minutes, add those minutes together and then convert it back to hours and minutes...possibly? Any ideas?

|||

YOu could use this function:

Code Snippet

CREATE FUNCTION dbo.TimeDiffInHoursAndMinutes
(
@.Firstdate DATETIME,
@.Seconddate DATETIME
)
/*
Function written by Jens K. Suessmeyer, 07/22/2007
http://www.sqlserver2005.de
*/
RETURNS VARCHAR(10)
AS
BEGIN

DECLARE @.FirstdateMinutes INT
DECLARE @.SeconddateMinutes INT

SELECT @.FirstdateMinutes = DATEPART (hh,@.Firstdate)*60 + DATEPART(mi,@.Firstdate)
SELECT @.SeconddateMinutes = DATEPART (hh,@.Seconddate)*60 + DATEPART(mi,@.Seconddate)

RETURN (
SELECT
CONVERT(VARCHAR(10), FLOOR(@.SeconddateMinutes-@.FirstdateMinutes) / 60) +
':' +
RIGHT('00' + CONVERT(VARCHAR(10), (@.SeconddateMinutes-@.FirstdateMinutes) - FLOOR((@.SeconddateMinutes-@.FirstdateMinutes) / 60)*60),2))
END;

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

This 'should' move you in the direction you want. (I've added another row to the sample data.)

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
TravelTime smalldatetime
)

INSERT INTO @.MyTable VALUES ( '01/02/2007 01:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/03/2007 01:45:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 03:00:00PM' )
INSERT INTO @.MyTable VALUES ( '01/04/2007 01:45:00PM' )

SELECT
Hours = sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 2 ) AS int )) +
( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) / 60 ) ,
Mins = ( sum( cast( parsename( replace( left( right( convert( varchar(20), TravelTime, 100 ), 7 ), 5 ), ':', '.' ), 1 ) AS int )) % 60 )
FROM @.MyTable

Hours Mins
-- --
7 30

|||Thank you so much Arnie that seems perfect. |||In addition and althrough already closed, I found something on my blog (didn′t know that I wrote such a thing yet :-) )

http://www.sqlserver2005.de/sqlserver2005/MyBlog/tabid/56/EntryID/31/Default.aspx

Jens K. Suessmeyer

http://www.sqlserver2005.de