CODEWITHSUNDEEP
c#sql-serverlinqentity-frameworktimespan
wjmolina
wjmolina

Reputation: 2675

Operand data type time is invalid for add operator

I have the following table:

                  SHIFT
 ----------------------------------------
| SHIFT_ID | SHIFT_TIME | SHIFT_DURATION |
| -------------------------------------- |
| 1        | 00:00:00   | 01:00:00       |
| 2        | 01:00:00   | 01:00:00       |
| 3        | 02:00:00   | 01:00:00       |
 ----------------------------------------

Here, SHIFT_TIME and SHIFT_DURATION are of type TimeSpan.

Now, when I run the following query:

var query = from c in SHIFT
            where c.SHIFT_TIME + c.SHIFT_DURATION >=
            new TimeSpan(DateTime.Now.Hour,
                         DateTime.Now.Minute,
                         DateTime.Now.Second)
            select c;

I get the following error: Operand data type time is invalid for add operator.

Why does it do that? How can I circumvent this error?

Edit: I have tried using both .Add() and .CompareTo() to no avail.

Upvotes: 3

Views: 3263

Answers (2)

Matt Johnson-Pint
Matt Johnson-Pint

Reputation: 241603

There are a few problems I see.

Let's start here:

new TimeSpan(DateTime.Now.Hour, DateTime.Now.Minute, DateTime.Now.Second)

Calling DateTime.Now multiple times is never a good idea. You're reading the system clock three times. Also, there is already a property for this exact purpose, so you can just do:

DateTime.Now.TimeOfDay

Next problem:

c.SHIFT_TIME + c.SHIFT_DURATION

If the shift time is 11:00 PM, and the duration is 2 hours, You might be expecting 1:00 AM, but instead you're going to get 25 hours. (Actually you'll get "1 day and 1 hour".) So when you it to the time of day (such as 12:30 AM), you're likely to be getting different results than you expect.

Next problem:

You failed to mention this, but it looks like you're actually using LINQ-to-Entities, as part of Entity Framework, attached to a SQL Server. (I updated your tags.) I can tell because the error message you are receiving is actually a SQL Server error message. You can reproduce it in SQL Server Management Studio like this:

declare @t1 time, @t2 time
set @t1 = '1:00'
set @t2 = '1:00'
print @t1 + @t2

Msg 8117, Level 16, State 1, Line 4
Operand data type time is invalid for add operator.

While you can add two TimeSpan types in .Net, you cannot add two time types in SQL Server. This is because time is meant to represent a time of day, while TimeSpan primarily represents a measured length of time. (Technically, the DateTime.TimeOfDay property I mentioned above goes against the design purpose of the TimeSpan type, but it is permitted because there is no Time type in .Net.)

So when you were doing your original query, the columns of time types were added together, and this is not allowed. The largest value of time type is 23:59:59.9999999, so it wouldn't be possible to get the 25 hours result I mentioned earlier.

What To Do?

To manipulate dates and times while you're in the SQL query, you need to use the methods of the EntityFuntions or SqlFunctions classes. These will translate to SQL's native functions in your query.

I believe this will do what you were looking for:

var query = from c in SHIFT
            where EntityFunctions.AddMinutes(c.SHIFT_TIME,
                    EntityFunctions.DiffMinutes(TimeSpan.Zero, c.SHIFT_DURATION))
                  >= DateTime.Now.TimeOfDay
            select c;

This will build a sql query similar to the following (assuming it is 1:00 right now):

SELECT * FROM SHIFT WHERE DATEADD(minute,
                                    DATEDIFF(minute, 0, SHIFT_DURATION),
                                    SHIFT_TIME
                                 ) >= '1:00'

SQL Server will implicitly cast the time input type to a datetime so it can be used with the DATEADD function. It will also cast the results back to a time type, so you can compare it against the "now" time you provided to the query.

Upvotes: 4

Jonesopolis
Jonesopolis

Reputation: 25370

It doesn't know how to add two timespans. From this site use TimeSpan.Add()

Then use TimeSpan.Compare()

are you doing 

var query = from c in SHIFT
        where TimeSpan.Compare(c.SHIFT_TIME.Add(c.SHIFT_DURATION), 
                new TimeSpan(DateTime.Now.Hour,
                             DateTime.Now.Minute,
                             DateTime.Now.Second)).Equals(1)
        select c;

?

Upvotes: 1

Related Questions