Substract and sum DateTime Values

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Substract and sum DateTime Values

Author

Topic

madmax81
Starting Member

4 Posts

Posted - 2006-08-19 : 13:45:31

Hello at all!

Is there any way to do datetime calculations like substracting or summing up time values within sql? For numbers there is the sum() function but how can I get the same functionality for datetime values(for example 10:00:00 + 05:00:00 + 09:30:00 +.....)? And what would be a way to substract datetime values(for example 10:00:00 - 05:00:00)?

I'd be very glad if there would be a possibility to accomplish these objectives.

Thanks in advance,
madmax

panthagani
Yak Posting Veteran

58 Posts

Posted - 2006-08-19 : 16:44:56

I don't think SQL server provides an out of the box solution to add time values. The most we can get to is, seperate the date from the time as shown below:

DECLARE @GETDATE DATETIME
DECLARE @PREVDATE DATETIME
SET @GETDATE = '2006-08-19 12:53:42.900'
SET @PREVDATE = '2006-08-12 07:19:00.000'
SELECT CONVERT(VARCHAR(15),@GETDATE,108)
SELECT CONVERT(VARCHAR(15),@GETDATE,108)

The above time values are character type and can't be CAST back to DATETIME - that's where the catch is :) Atleast, I couldn't find a way to do so.. You can try to build a user defined function to 'add' these time values - like wrapping back to 0 if sum of hours is greater than 23, sum of minutes is greater than 59 etc...

panthagani

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-19 : 20:15:56

You can add and subtract datetime values, but you cannot use them in a SUM().

You can read the "Uses of the DATETIME data type" section on the link below for examples.

Date/Time Info and Script Links
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=64762

CODO ERGO SUM

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 01:45:03

There is a function called as DateAdd in SQL Server using that, you can add the dates.

But explain to us what extactly you are trying to do, post same sample data??

Chirag

madmax81
Starting Member

4 Posts

Posted - 2006-08-20 : 03:23:20

Well,the reason why I need to do such time calculations is that I'm programming a time management software in ASP.Net. Therefore I often have to calculate with time values.

Let me put a finer point on it:

I have a page where the users book their working time. When a user starts working he books in for example at 08:00 and books out at 12:00 because he has a break for an hour. At 13:00 he again starts working and stops at 17:00.

The following data is written into the database:



UserName|DayOfWeek|Date|BookedIn|BookedOut|Period
user1|Saturday|2006-08-19 00:00:00|2006-08-19 08:00:00|2006-08-19 12:00:00|2006-08-19 04:00:00	
user1|Saturday|2006-08-19 00:00:00|2006-08-19 13:00:00|2006-08-19 17:00:00|2006-08-19 04:00:00
user1|Sunday...........
user1|Monday...........

If I want to know how many hours and minutes the user has worked this week or the last month I have to sum the values of the Period column and the result must have the format hh:mm. An other problem that appears is that there are more than 24 hours in the result of the calculations.

For other calculations I must substract such time values like 2006-08-19 12:00:00 - 2006-08-19 04:00:00 = 2006-08-19 08:00:00

At the moment I'm doing this calculations in my program but it would be much more easier to do it directly with sql take the result and continue to work with it in the application.

I hope my explanations are understandable to you in some degree.

If there at least would be a possibility to make my sums with sql it already would save me a lot of labor.

Thank you for your answers so far!!

madmax

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 04:15:46

You can make use of sum, using some of the date functions, read on Book online for the details.

The query should look somthing like this


Select UserName,Convert(varchar(10),sum(DatePart(hh,Period)))+':'+
Convert(varchar(10),sum(DatePart(mi,Period))) [Total Hours]
 From @TblBooks
Group by username

The Above query will give you the list of username and their working hours. in HH:MM format.

What you want to do if the number of hours are greater then 24 then what should be format in that case??

quote:
For other calculations I must substract such time values like 2006-08-19 12:00:00 - 2006-08-19 04:00:00 = 2006-08-19 08:00:00

What does this means???

Chirag

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 04:43:43

I am confused what you Edited?

Chirag

madmax81
Starting Member

4 Posts

Posted - 2006-08-20 : 05:09:34

Hello Chirag!

Thank you for your reply!

The statement for summing helps me for sure.

It's the first time that I have to calculate with time values in that dimension and it seems to be very tricky to me.

quote:
What you want to do if the number of hours are greater then 24 then what should be format in that case??

The format should be hhh:mm.
For example: 60:23 or 120:40

quote:

quote:
--------------------------------------------------------------------------------
For other calculations I must substract such time values like 2006-08-19 12:00:00 - 2006-08-19 04:00:00 = 2006-08-19 08:00:00
--------------------------------------------------------------------------------

What does this means???

Here is a short example:
The regular working time of a user is 08:00 hours:minutes but he works for 10:30 hours:minutes. In this case he makes overtime. If I want to calculate how much overtime he made I've to do following calculation: 10:30 - 08:00 and the result should be 02:30
So I know that he made 02:30 hours:minutes of overtime on Monday.

regards,
madmax

madmax81
Starting Member

4 Posts

Posted - 2006-08-20 : 05:19:05

Dont't worry, I've just corrected a few spelling mistakes that I found when I read my post again.

My English is still 'under construction'.

regards,
madmax

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2006-08-20 : 05:45:02

quote:
The format should be hhh:mm.
For example: 60:23 or 120:40

I guess this should be fine with the query which i gave, only thing is that what if the min is greater then 60 then, you need to add in the min s part??

quote:
The regular working time of a user is 08:00 hours:minutes but he works for 10:30 hours:minutes. In this case he makes overtime. If I want to calculate how much overtime he made I've to do following calculation: 10:30 - 08:00 and the result should be 02:30
So I know that he made 02:30 hours:minutes of overtime on Monday.

This can be acheived with the datediff function.
Somthing like this


Select Convert(varchar(10),Datediff(mi,'1900-01-01 08:00:00','1900-01-01 10:30:00')/ 60) +':' + 
Convert(varchar(10),Datediff(mi,'1900-01-01 08:00:00','1900-01-01 10:30:00')%60)

quote:

My English is still 'under construction'.

it was very much understandable.

Chirag

lockebkbe
Starting Member

1 Post

Posted - 2010-05-12 : 00:17:51

Hi all,

Also relating to this question, is there anyway to do the following:

ID STATUS DATE
1 NEW 1/29/2010 2:33:09PM
1 INPRO 4/19/2010 8:42:31AM
2 NEW 12/31/2009 10:00:41AM
2 INPRO 1/1/2010 8:23:42AM

The objective is to query all those records in the table with status changes from NEW to INPRO in less than or equal to 72 hours.

I also notice that this is SQL Server 2000 topic. But my DB is in Oracle and I'd be glad if there's any solution to this.

Thanks in advance.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources