How to subtract hours using DbFunctions?

Question

I'm using .Net framework 4.5 and Entity Framework 6.0

Here's the code I wrote.

var _temp = (from x in context.LOG_ACTIVITIES
             ...
             select new 
             {
               RECEIVED_DATE = DbFunctions.AddHours(x.RECEIVED_DATE, -8), 
             ...
             }
            );

Here's the exception message thrown.

SqlDbType.Time overflow. Value '-08:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999.

Any help would be appreciated. TIA!

What is wrong with the code you have got, it should subtract 8 hours. — Ben Robinson, Sep 07 '15 at 08:30
Here the exception message thrown when the code was executed. SqlDbType.Time overflow. Value '-08:00:00' is out of range. Must be between 00:00:00.0000000 and 23:59:59.9999999. — Vond Ritz, Sep 07 '15 at 08:32
I suggest that you evaluate `ToString()` on your query to see the generated SQL. From the exception message it is obvious that there is a SQL Server `time` data type somwhere in your query but the C# snippet you have provided will not generate that error provided that `RECEIVED_DATA` is of type `datetime` so there is something missing. — Martin Liversage, Sep 07 '15 at 08:58
Hi Martin Liversage, I found the issue why dbfunction don't work when I evaluate the generated sql. I solved it now. Thank you for your suggestion. — Vond Ritz, Sep 07 '15 at 10:16

score -1 · Answer 1 · answered Sep 07 '15 at 09:16

The error message is clear. The result of function is negative and the datatype "SqlDbType.Time" can not handle that. You need use another datatype (i don't know, if you can). Or you need check for valid input (if the "RECEIVED_DATE" is greater than "8 hours") and return flag if result value is valid. For invalid result you can use "NULL".

How to subtract hours using DbFunctions?

1 Answers1