CODEWITHSUNDEEP
sqlsql-serverdatetimesumintervals
RobertHaa
RobertHaa

Reputation: 79

SQL operand data type datetime / varchar is invalid for sum operator

I am trying to subtract one column from another column, and then sum the result. However, whatever I change I keep getting one of the two errors as mentioned in the title.

From all other questions posted, I derived that my statement should be something like:

SELECT SUM(EndTime-BeginTime) AS TotalTime FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

As per suggestion in another topic, I changed the statement to:

SELECT SUM(CONVERT(varchar(EndTime-BeginTime), 108)) AS TotalTime FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

However, I still receive the error. The times are stored as follows:

1-1-1900 7:30:00

Thanks for any suggestion...

Edit 1: Oh, I am using a Microsoft Query through ODBC for SQL in Excel, kind of like: Excel vlookup incorporating SQL table

Edit 2: I prefer to have the output again in the HH:MM format.

The thing is, when I copy the complete database to excel, everything works just fine. I can subtract the columns and sum up. I just dont want to that manually every day....

I just checked the SQL database, and the column is of Date/Time type.

Upvotes: 0

Views: 7233

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131209

First of all, trying to solve date or numeric issues by converting to text never solves anything, it adds additional problems like failed conversions or unexpected results.

SQL Server doesn't have a time interval type. If you subtract two datetime values you get back another datetime value that represents the difference as the offset since 1900-01-01. The following query :

select getdate() - dateadd(d,-1,getdate())

Will return :

1900-01-02 00:00:00.000

Which is 1 full day after 1900-01-01.

datetime values can't be summed though. SQL Server does have a time type but that just represents the time of day. Even if today - yesterday worked, converting that to time would return 00:00.

A quick solution is to use DATEDIFF to calculate the difference between two dates in whatever unit is required - hourss, minutes, seconds etc. Be aware though that DATEDIFF returns the number of interval boundaries crossed. DATEDIFF(DAY,...) between 11pm yesterday and 1am today will return 1, because 1 day boundary was crossed.

You can use 

SELECT sum(datediff(minute,EndTime,BeginTime)) AS TotalMinutes
FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

To calculate the difference in minutes and format it as a time interval on the client.

Another option is to cast the datetime to a float before summing, then back to datetime: 

SELECT cast( sum(cast(EndTime - BeginTime as datetime)) as datetime) AS TotalOffset
FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

A 2-day duration would appear as :

1900-01-03 00:00:00.000

This works because datetime can be cast to a float whose integral part represents the offset from 1900-01-01 and the fractional part the time.

A client written in a language like C# that does support intervals could subtract 1900-01-01 from this to get back the actual duration, eg :

TimeSpan duration = sumResult - new DateTime(1900,1,1);

Another option would be to avoid the final cast and just use the resulting float value as the number of days.

Displaying in Excel

The last option could be the easiest way to display the duration in Excel! Excel dates are actually stored as floats. What makes them appear as dates or times is the cell's formatting.

You can set a cells format to [h]:mm:ss to display any number as a duration. The [] is important - without it Excel will only display the time part of the "date".

If you enter 2 in a cell, h:mm:ss will show it as 0:00:00 while [h]:mm:ss will display 48:00:00, 48 hours.

Despite its strangeness, 

SELECT sum(cast(EndTime - BeginTime as datetime)) AS TotalDuration
FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

May be the best option to display a duration sum in Excel

Upvotes: 2

Barry Piccinni
Barry Piccinni

Reputation: 1791

How about the following: 

WITH totalTime AS (
  SELECT SUM(DATEDIFF(second,  EndTime, BeginTime)) as TimeInSecond
  FROM TimeRegister 
  WHERE OrderNumber = 00000 AND Activity = 11111;
)
SELECT RIGHT('0' + CAST(TimeinSecond / 3600 AS VARCHAR),2) + ':' +
  RIGHT('0' + CAST((TimeinSecond / 60) % 60 AS VARCHAR),2) + ':' +
  RIGHT('0' + CAST(TimeinSecond % 60 AS VARCHAR),2) AS TimeElapsed
FROM totalTime;

In this solution, the time is converted into seconds, before being totalled, and then converted to HH:MM:SS.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269533

You seem to want datediff():

SELECT SUM(DATEDIFF(second, BeginTime, EndTime) AS TotalTime
FROM TimeRegister 
WHERE OrderNumber = 00000 AND Activity = 11111;

Upvotes: 0

Related Questions