Odata v4 $fitler datetimeoffset using logical operators such as greaterthanorequal

Topics: ASP.NET Web API
Jul 17, 2014 at 9:18 PM
Hi,

This is probably something simple but have been struggling, sorry.

As support for DateTime has been dropped for v4, i've changed DateTime's to DateTimeOffset

Now when trying to $filter a resource based on being after a DateTime, i'm running into problems.

I had this working before upgrading to use Odata v3 whilst using DateTime. However i'm struggling to get what i want after upgrading.


Testing the web api using fiddler, I get an error when querying with the following filter.

Query
http://localhost/api/tbCourses?$filter=crs_Start%20ge%20cast(%272014-07-01T00:00:00+00:00%27,Edm.DateTimeOffset)

Error
ExceptionMessage=The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTimeOffset]' and 'System.Object'.

Is my cast incorrect?

Can i use logical operators (ge) with DateTimeOffset?

Can i use logical operators with Nullable DateTimeOffset?

Thanks all for you help.
Jul 18, 2014 at 4:06 PM
Hi,

Now in webapi.odata "cast" function doesn't support to cast from string to DateTimeOffset.

However your string to be cast is string constant rather than string variable.
For such case, you can write it as DateTimeOffset without quotes in url instead of string.

Just like:
http://localhost/api/tbCourses?$filter=crs_Start%20ge%202014-07-01T00:00:00%2B00:00
(Note: "+" needs to be escaped to "%2B")

Or remove the quotes and keep the "cast" which is used to cast from DateTimeOffset to DateTimeOffset.
Just like:
http://localhost/api/tbCourses?$filter=crs_Start%20ge%20cast(2014-07-01T00:00:00%2B00:00,Edm.DateTimeOffset) 
Hope this will help you.
Marked as answer by agccheung on 7/20/2014 at 1:23 PM
Jul 20, 2014 at 8:23 PM
Perfect! That did the job. Thank you very much. :-)
Feb 10, 2015 at 9:39 AM
Hi,

I have a similar problem with OData, and I wonder if you have any tips to help me because I have run out of ideas :(

"and cast(Cr_time_db,'Edm.DateTimeOffset') ge 2000-08-09T00:00:00%2B02:00 and cast(Cr_time_db,'Edm.DateTimeOffset') le 2014-10-08T00:00:00%2B02:00"

this works for me but i have to do it manually...

in my database cr_time_db is of the type DateTime, in my odataClient it is of type DateTimeOffset, this cast will Odata do for me.

but when i use the "container" whit some linq to generate the url it will be: "and Cr_time_db ge 2000-08-09T00:00:00%2B02:00 and Cr_time_db le 2014-10-08T00:00:00%2B02:00"

and this Error will occur:
ExceptionMessage=The binary operator GreaterThanOrEqual is not defined for the types 'System.Nullable`1[System.DateTimeOffset]' and 'System.Object'.

Thanks!
Feb 11, 2015 at 6:11 AM
Tried casting 2000-08-09T00:00:00%2B02:00 ?
Feb 11, 2015 at 6:22 AM
are not sure what you mean?, but I do a cast in the url manually " cast(Cr_time_db,'Edm.DateTimeOffset') ge 2000-08-09T00:00:00%2B02:00 "
and it works, bur how do i tell odata to do this automatic.


DateTime? fromDate, DateTime? toDate

var result = _container.S.Where(SearchS(fromDate, toDate));

private static Expression<Func<S, bool>> SearchS(DateTime? fromDate, DateTime? toDate)
return s.Cr_time_db >= fromDate && s.Cr_time_db <= toDate;

this will generate this url : "and Cr_time_db ge 2000-08-09T00:00:00%2B02:00 and Cr_time_db le 2014-10-08T00:00:00%2B02:00"
but i whant this: "and cast(Cr_time_db,'Edm.DateTimeOffset') ge 2000-08-09T00:00:00%2B02:00 and cast(Cr_time_db,'Edm.DateTimeOffset') le 2014-10-08T00:00:00%2B02:00"