2

The below two queries are identical except that the first has >= on the last line and the second has =. Yet the first returns 129 rows, while the second returns 0. Today is 11-15-2016, so I expected them to return the same. The data type of date_time is date.

Can someone explain why simply changing the operator from >= to > changes the result? Thanks,

select orderid
    from order_log
    where order_version = 0
    and description = 'Order Complete'
    and date_time **>=** to_date('11-15-2016', 'MM-DD-YYYY')

select orderid
    from order_log
    where order_version = 0
    and description = 'Order Complete'
    and date_time **=** to_date('11-15-2016', 'MM-DD-YYYY')
Matt
  • 13,833
  • 2
  • 16
  • 28
Seth A Kelley
  • 33
  • 1
  • 4
  • Have you looked at the full time stamps including hours, minutes, seconds, etc.? Seems there are orders that are coming in with a time stamp of 11-16-2016. Is yours an international website? Are there orders coming in from other time zones with local time stamps? Are all dates converted to utc or a single time zone? – mba12 Nov 15 '16 at 22:01
  • 1
    Thanks mba12, I'm not sure if dates are local or UTC, or if there is some conversion happening, but all orders are entered during ~normal business hours in one of two time zones here in the US, so I don't think that could be it. – Seth A Kelley Nov 16 '16 at 15:09

1 Answers1

5

date_time is a timestamp (date + time) and you don't have events that occurred exactly at midnight (2016-11-15 00:00:00).

select          sysdate
               ,case when sysdate = date '2016-11-16' then 'Y' else 'N' end as is_equal

 from           dual

SYSDATE             IS_EQUAL
2016-11-16 00:23:37 N
David דודו Markovitz
  • 42,900
  • 6
  • 64
  • 88