sql转换日期转换错误的原因是什么?

I'm using the following code:

我使用的代码如下:

sqlcom.CommandText = "SELECT * FROM myTable"
                    + " WHERE CAST(myTime AS DATE) >= CAST(@mySTime AS DATE)"
                    + " AND CAST(myTime AS DATE) <= CAST(@myETime AS DATE)"
                    + "order by myTime ";

sqlcom.Parameters.AddWithValue("@mySTime", stime);
sqlcom.Parameters.AddWithValue("@myETime", etime);

stime and etime are both DateTime columns. The following is an abbreviation of the code that sets them:

stime和etime都是DateTime列。下面是设置它们的代码的缩写:

sTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1);

eTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1).AddDays(1).AddMilliseconds(-1);

Which for example leads to:

例如，它导致:

sTime = '2015-10-19 00:00:00';
eTime = '2015-10-19 23:59:59';

when displayed in the debugger (stime and etime have a few other options how they could be set that is why the sql is dynamically taking them in but in this current case the above holds true).

当在调试器中显示时(stime和etime还有一些其他选项可以设置它们，这就是为什么sql会动态地接收它们，但在当前情况下，上面的选项是正确的)。

Now when I run the above SQL I get everything even from the current day! BUT when I change AS DATE to AS DATETIME it works as intended that I just get the LAST day and nothing from today.

现在，当我运行上面的SQL时，我甚至可以从当前日期获得所有的信息!但当我将日期改为DATETIME时，它的作用是我只得到最后一天，而不是今天。

Now my question is: Is there any reason why the original sql/date comparison fails? (could it be because of that it is just the millisecond -1 that it rounds it up to the next day? OR is there any other reason there?)

现在我的问题是:为什么原始的sql/日期比较失败了?(是不是因为这只是毫秒-1，它就会四舍五入到第二天?)或者还有其他原因吗?

1 个解决方案

#1

eTime = new DateTime(DateTime.Now.Year, DateTime.Now.Month, DateTime.Now.Day, 0, 0, 0).AddDays(-1).AddDays(1).AddMilliseconds(-1);

结束= new DateTime(DateTime.Now。年,DateTime.Now。月,DateTime.Now。一天,0,0,0).AddDays(1).AddDays(1).AddMilliseconds(1);

Why are you using .AddDays(-1).AddDays(1). It seems useless.

为什么要用。adddays(-1)。adddays (1)似乎毫无用处。
.AddMilliseconds(-1)

.AddMilliseconds(1)

Your data type is datetime. datetime has an accuracy of 3ms with increments of .000, 003 or .007 seconds. Therefore, any of these 3 values minus 1 (ms) is always rounded back to the original value:

您的数据类型是datetime。datetime的准确度为3ms，增量为0.000、003或0.007秒。因此，这3个值- 1 (ms)中的任何一个总是被圆回到原来的值:

xxx.000 - .001 = .999 => rounded to .000
xxx.003 - .001 = .002 => rounded to .003    
xxx.007 - .001 = .006 => rounded to .007

This seems useless as well.

这似乎也没用。

Round up

围捕

'2015-10-19 23:59:59' won't be rounded but '2015-10-19 23:59:59.999' will be round up to '2015-10-20 00:00:00.000' because 999 is surrounded by 997 and 000. 000 is the closest value.

“2015-10-19:59:59”不会四舍五入，但是“2015-10-19:59:59 - 999”会加到“2015-10-20:00:01:000”，因为999被997和000包围着。000是最接近的值。

<= 18-10-2015 23:59:59

< = 18-10-2015 23:59:59

You will miss any time above 23:59:59.000 and below of equal to 23:59:59.997

你将错过任何时间在23:59:59万以下，低于23:59:59.997。

CAST(myTime AS DATE)

铸造(myTime日期)

This will most likely prevent the usage of index on myTime. It should not be used.

这很可能会阻止在myTime上使用索引。它不应该被使用。

It is fine to stick to datetime although datetime2 would be a better choice. If you are looking for value on a specific day, you must look for value between DAY at 00:00:00 and the next day at 00:00:00.

尽管datetime2是更好的选择，但是坚持使用datetime是可以的。如果你想在特定的一天寻找价值，你必须在每天的00:00:00和第二天的00:00点寻找价值。

You can find a lot of useful information about date comparison on most of the answer here, including my own anwser: Why does my query search datetime not match?

您可以在这里找到关于日期比较的许多有用信息，包括我自己的anwser:为什么我的查询搜索日期时间不匹配?

#1