Issue with Odata V4 DateTimeOffset filter

asp.net c# entity-framework-6 odata

Question

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?

1
3
4/27/2015 6:00:21 PM

Accepted Answer

I'm seeing your question today. To add a filter to an OData URI, add $filter=field_name gt 2018-02-22T00:00:00Z

Do not enclose the value in single quotes. I found out here: http://docs.oasis-open.org/odata/odata/v4.0/odata-v4.0-part2-url-conventions.html - search for 5.1.1.11.1 Primitive Literals within the document.

2
2/26/2018 11:24:20 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow