CODEWITHSUNDEEP
sql-serverdatetime
Ege Bayrak
Ege Bayrak

Reputation: 1199

Sql server greater than operator gives wrong result on datetime

I just came across this and it looks weird. I can't understand why this is happening so I hope someone can explain it

My query is very simple 

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) is returning this (beginning of today)

2016-10-10 00:00:00.000

Here the weird stuff begins, notice the first two rows

+---------+------------+-------------------------+
| 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 |
+---------+------------+-------------------------+

Does greater than operator have a different way of working with datetime types?

Upvotes: 1

Views: 82

Answers (2)

TheGameiswar
TheGameiswar

Reputation: 28930

Try changing

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

to

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

Upvotes: 2

Mojo Allmighty
Mojo Allmighty

Reputation: 793

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

Maybe the date stored in mysql doesn't have datetime format ?

Upvotes: 0

Related Questions