How to add day to date in Linq to SQL

Question

I am writing this code. Here dt is input into the function, as well as someint. The column Exp is a T-SQL date column, which comes as a DateTime through Linq.

return (from a in dataContext.TableOfA
       where a.name == "Test" &&
       a.Exp.Value.AddDays(Convert.ToDouble(Someint)) >= new DateTimeOffset(dt)
       select a).First();

In C#, you can add a double as a day to a date time. Meaning you can add 1.5 days. In T-SQL you can only add 1 day, then 12 hours. You must add an int for each part. So when Linq translates AddDays to T-SQL, it converts my number of days to milliseconds, and adds those. This allows it to give all the precision the double gives C#.

Here's the rub. When this gets to SQL, I get the error:

The datepart millisecond is not supported by date function dateadd for data type date

Basically you can't add milliseconds to a date. Well no kidding. But how do I get something that translates here? I want to add int days to a date. Is the only want to do this to add the negative of them to the other guy I am comparing against? What if I wanted to compare to columns while adding to one?

Update 1

Keith wrote, A command like datepart(millisecond, 10000, myDate) has been supported in T-SQL since at least SQL Server 2000. This error suggests that whatever database you are using does not support the millisecond date part, which seems strange to me.

Please note I am using SQL Server 2008. It is not supported on the DATE data type. It is supported on datetime.

Answer 1

If you are using the Entity Framework, use the System.Data.Objects.EntityFunctions as below:

c.CMT_TS > System.Data.Objects.EntityFunctions.AddDays(e.Call.CALL_TS, 1)

Answer 2

I just changed the column back to a DateTime.

Answer 3

Create a new DateTime object and use AddDays method after that:

new DateTime(t.Key.Year,t.Key.Month,T.Key.Day).AddDays(xx)

Answer 4

You can use: System.Data.Entity.DbFunctions.AddDays(YourDateTime, NumberOfDays) This function will be translated to valid SQL Query. Class System.Data.Entity.Core.Objects.EntityFunctions which also contains AddDays function is obsolete.