大于运算符的Sql server在日期时间上会产生错误的结果。

Question

我刚发现这件事看起来很奇怪。我不明白为什么会发生这种事，所以我希望有人能解释一下

我的查询很简单

SELECT  OrderID, CustomerID,OrderDate  FROM  PrmOrder PO 
WHERE 1=1
AND PO.OrderStatusID != 1
AND PO.IsOrderApproved = 1
AND PO.IsInventoryApproved = 0
AND (PO.OrderStatusID = 2 OR PO.OrderStatusID = 3
AND PO.OrderDate >= dateadd(day, datediff(day, 0, getdate()),0))

select dateadd(day, datediff(day, 0, getdate()),0)将返回这个(今天开始)

2016-10-10 00:00:00.000

奇怪的事情开始了，注意前两行

+---------+------------+-------------------------+
| OrderID | CustomerID |        OrderDate        |
+---------+------------+-------------------------+
|  204634 |     273952 | 2016-10-07 09:29:11.990 |
|  202224 |     274450 | 2016-10-08 13:55:54.613 |
|  205479 |     275010 | 2016-10-10 09:09:26.710 |
|  205185 |     275014 | 2016-10-10 09:42:11.587 |
|  205024 |     274595 | 2016-10-10 10:47:46.983 |
|  196984 |     220600 | 2016-10-10 10:56:45.227 |
|  204830 |     274330 | 2016-10-10 13:26:45.657 |
|  205494 |     275043 | 2016-10-10 11:16:57.220 |
|  205500 |     275031 | 2016-10-10 10:50:45.687 |
|  205512 |     275044 | 2016-10-10 11:17:39.590 |
|  205520 |     275090 | 2016-10-10 12:56:59.910 |
|  205525 |     275084 | 2016-10-10 12:34:47.317 |
|  205530 |     275079 | 2016-10-10 12:24:34.217 |
|  205531 |     275083 | 2016-10-10 12:28:25.597 |
|  205532 |     275085 | 2016-10-10 13:07:21.657 |
|  205537 |     275089 | 2016-10-10 13:03:10.037 |
|  205539 |     275100 | 2016-10-10 13:27:22.647 |
|  188617 |     247554 | 2016-10-10 13:54:22.833 |
|  204265 |     274312 | 2016-10-10 15:58:13.840 |
|  204409 |       9370 | 2016-10-10 13:36:48.963 |
|  205282 |     273520 | 2016-10-10 14:47:07.487 |
|  205549 |     275105 | 2016-10-10 13:41:44.940 |
|  205564 |     275138 | 2016-10-10 14:47:32.420 |
|  205568 |     275154 | 2016-10-10 16:05:48.547 |
|  205575 |     275150 | 2016-10-10 15:28:06.147 |
+---------+------------+-------------------------+

大于操作符是否有不同的处理日期时间类型的方法？

Answer 1

试着改变

AND (PO.OrderStatusID = 2 OR PO.OrderStatusID = 3
AND PO.OrderDate >= dateadd(day, datediff(day, 0, getdate()),0))

至

AND (PO.OrderStatusID = 2 OR PO.OrderStatusID = 3)
and PO.OrderDate >= dateadd(day, datediff(day, 0, getdate()),0)

Answer 2

Select * From table 
where STR_TO_DATE(mydate, '%Y-%m-%d %H:%i:%s') >= dateadd(day, datediff(day, 0, getdate()),0);

也许mysql中存储的日期没有日期时间格式？