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

Have you considered writing your own user-defined function that basically takes in an int (for number of days) and date, returns the results of a dateadd()? Then pull that into the LINQ class in your project. Then, instead of calling .AddDate(), call your UDF - this will allow you to keep everything in your LINQ query without having to manually piece together a SQLCommand.

Answer 2

I just changed the column back to a DateTime.

Answer 3

You can also use SqlMethods.DateDiffDay method, if you're trying to compare two dates