如何在 Linq to SQL 中添加日期到日期

问题描述

我正在编写这段代码.这里 dt 是函数的输入，还有 someint.Exp 列是一个 T-SQL 日期列，通过 Linq 作为 DateTime 出现.

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();

在 C# 中，您可以向日期时间添加双精度值作为一天.这意味着您可以添加 1.5 天.在 T-SQL 中，您只能添加 1 天，然后是 12 小时.您必须为每个部分添加一个 int.因此，当 Linq 将 AddDays 转换为 T-SQL 时，它会将我的天数转换为毫秒，然后添加这些天数.这允许它提供 double 给 C# 的所有精度.

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

麻烦来了.当这到达 SQL 时，我收到错误:

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

日期部分毫秒不是由日期函数 dateadd 支持数据类型日期

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

基本上你不能给日期加上毫秒.好吧，别开玩笑了.但是我如何得到在这里翻译的东西?我想在日期中添加 int 天.这样做的唯一目的是将它们的负面影响添加到我与之比较的另一个人中吗?如果我想在加一的同时与列进行比较怎么办?

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?

更新 1

Keith 写道，一个命令就像日期部分(毫秒，10000，我的日期)自从在 T-SQL 中得到支持至少 SQL Server 2000.这个错误建议您使用任何数据库正在使用不支持毫秒日期部分，这似乎对我来说很奇怪.

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.

请注意我使用的是 SQL Server 2008.它不支持 DATE 数据类型.支持日期时间.

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

推荐答案

我刚刚将列改回 DateTime.

I just changed the column back to a DateTime.