Add times

SQL Statement.

Adding times together.

I am trying to add a list of times together retrieved from my database.

They are from seperate records in the same table.

They are in a date format e.g. 01-Jan-2006 03:45:00, what I want to do is ignore the 01-Jan-2006 part as well as the :00 AM part and focus on the hours and minutes. I wish to add these up e.g. 03:45 + 04:45 = 08:30. Is there an easy way of doing this?

Below is the SQL statement of getting times from my database if its any help.

SQL Statement

SELECT LENGTH_OF_VISIT FROM NOV.MAIN;

Returned Results

30-Apr-2007 02:00:00 AM

30-Apr-2007 01:00:00 AM

01-Jan-2006 02:30:00 PM

01-Jan-2006 03:10:00 PM

30-Apr-2007 01:00:00 AM

Hi Ric

This forum is specific to SQL Server Data Mining. You'll probably get a better answer by posting to the T-SQL forum at http://forums.microsoft.com/MSDN/ShowForum.aspx?ForumID=85&SiteID=1

You can extract the parts of your datetime field using the DATEPART function as:

SELECT DATEPART(LENGTH_OF_VISIT, hh) as Hour, DATEPART(LENGTH_OF_VISIT, mm) as AS Minute from Nov.Main.

Then you can wrap an aggregate ADD function to add the values from different records.

SELECT ADD(DATEPART(LENGTH_OF_VISIT, hh) * 60 + DATEPART(LENGTH_OF_VISIT, mm)) from Nov.Main

Please look at the documentation of DATEPART for more usage examples

Hope this helps