操作数数据类型时间对于加法运算符无效 [英] Operand data type time is invalid for add operator

问题描述

我有下表:

                  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       |
 ----------------------------------------

此处， SHIFT_TIME 和 SHIFT_DURATION 的类型为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;

我收到以下错误:Operand data type time is invalid for add operator.

为什么要这么做?我该如何避免这个错误?

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

编辑:我尝试同时使用.Add()和.CompareTo()无济于事.

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

推荐答案

我看到了一些问题.

让我们从这里开始:

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

多次调用DateTime.Now绝不是一个好主意.您正在读取系统时钟三遍.另外，已经有一个用于此确切目的的属性，因此您可以执行以下操作:

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

下一个问题:

c.SHIFT_TIME + c.SHIFT_DURATION

如果轮班时间为11:00 PM，且持续时间为2个小时，则您可能期望为1:00 AM，但是您将获得25个小时. (实际上，您会得到"1天1小时".)因此，当您将其设置为一天中的某个时间(例如12:30 AM)时，可能会得到与预期不同的结果.

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.

下一个问题:

您没有提到这一点，但是看起来您实际上是在使用LINQ-to-Entities，它是连接到SQL Server的实体框架的一部分. (我更新了标签.)我可以知道，因为您收到的错误消息实际上是SQL Server错误消息.您可以像这样在SQL Server Management Studio中重现它:

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

信息8117，第16级，状态1，第4行
操作数数据类型的时间对于加法运算符无效.

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

虽然可以在.Net中添加两种TimeSpan类型，但是不能在SQL Server中添加两种time类型.这是因为time表示一天中的某个时间，而TimeSpan主要表示一天中的某个时间长度. (从技术上讲，我上面提到的DateTime.TimeOfDay属性与TimeSpan类型的设计目的背道而驰，但是由于.Net中没有Time类型，因此可以使用它.)

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.)

因此，当您执行原始查询时，time类型的列被添加在一起，这是不允许的. time类型的最大值是23:59:59.9999999，因此不可能获得我之前提到的25小时结果.

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.

做什么?

要在SQL查询中操作日期和时间，需要使用

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;

这将建立类似于以下内容的sql查询(假设现在是1:00):

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会将time输入类型隐式转换为datetime，因此可以与DATEADD函数一起使用.还将结果转换回time类型，因此您可以将其与提供给查询的现在"时间进行比较.

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.

这篇关于操作数数据类型时间对于加法运算符无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！