I'm trying to filter a date field in an OData call by values which are either NULL or Greater Than the current date. I have tried all of the different things I could find on the internet, and here on SO. So far nothing works. It seems like it will work with a day-barrier, so things with an expiration date of yesterday don't show up but things with an expiration date of tomorrow will. However, if I have an expiration that's within an hour of the current time, it seems like neither of the results show up. For example: right now is 08:58. If one item expires at 08:00 and one expires at 09:00, neither will show up in my results.
I have reviewed the time zone information and the results coming back have the same time zone I'm setting for my filter.
Filter string template for date:
ExpirationDate eq null or ExpirationDate gt cast({0}, Edm.DateTimeOffset)) and Variations/any()
Code to fill it in:
var utcOffset = TimeZone.CurrentTimeZone.GetUtcOffset(DateTime.Now);
var timeStamp = DateTime.UtcNow.ToString("yyyy-MM-ddTHH:mm:ss");
var utcInfo1 = (utcOffset < TimeSpan.Zero) ? "-" : "+";
var utcInfo2 = utcOffset.ToString(@"hh\:mm");
_filterBuilder.Append(String.Format(_filterCORE, timeStamp + utcInfo1 + utcInfo2));
The above code is the original string I was attempting, without any of the changes I made to try and tweak it and get it working. I'm pretty sure UtcNow is not the right time value to be using for this query but I could be wrong. Code is C#.
UPDATE: Database value is stored as smalldatetime. Does/should that make a difference in the ability to compare the two dates?
What do I need to do to this filter to get results within an hour or minutes or seconds of the time an item is set to expire?
