locked
Issue with OData V4 DateTimeOffset filter RRS feed

  • Question

  • User-663334391 posted

    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. 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 (C#):

        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#.  _filterCORE is the variable for the format string above.

    As an update - the date column I'm trying to filter on is a smalldatetime in the database.  I don't know if that makes a difference because it looks to me like they are translated by the application as datetimeoffset values of whichever timezone the server is set to.

    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?

    Thursday, April 23, 2015 11:32 AM

All replies

  • User1644755831 posted

    Hello nulloverride,

    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 am not able to see your oDataQuery syntax. Can you please clarify is it really odata query issue? You could try to ask this question on odata forum: https://github.com/OData/odata.net/issues

    With Regards,

    Krunal Parekh

    Friday, April 24, 2015 4:14 AM
  • User-663334391 posted

    I put in the part of the query that makes a difference.  It works fine for all the other conditions in the filter, and for bigger differences than just an hour or so.

    Just replace my {0} in the "format string" above with a datetimeoffset value with +05:00 as the time zone and it should be valid.

    Friday, May 1, 2015 10:59 AM
  • User-663334391 posted

    So far github has been pretty useless.  So hopefully this site picks up a little bit.  All I really want is either a solution or someone to tell me "there's no way in he** you're going to get this to work because XXX".

    Friday, May 8, 2015 12:01 PM