Odata filter DateTimeOffset less that date

user_12647399 0
Viewed 6045

I am trying to retrieve all records before specific date as follow:

?$filter=CreatedDate lt '2020-06-04T14:27:12.38'

but i keep receiving this error

"message": "The query specified in the URI is not valid. A binary operator with incompatible types was detected. Found operand types 'Edm.DateTimeOffset' and 'Edm.String' for operator kind 'GreaterThan'.",

I tried to cast date 

    ?$filter=CreatedDate lt cast('2020-06-04T14:27:12.38', Edm.DateTimeOffset))

but still the same .

also tried 

?$filter=CreatedDate lt datetime'2020-06-04T14:27:12.38'

and received

The query specified in the URI is not valid. Unrecognized 'Edm.String' literal 'datetime'1995-09-01T00:00:00'' at '21' in 'CreatedDate gt datetime'1995-09-01T00:00:00''.

is there anyway to achieve that ?

odata sql
4 Answers

You have to query date without quotation marks. An example from my application:

http://localhost/EDS4OData/EmployeeHoursVacationRequests?$filter=(Username eq 'jsmith' and DeleteInd eq false and DayOffType eq 2 and StartDate ge 2020-10-01T00:00:00.000Z and StartDate le 2020-12-31T23:59:59.999Z)&$count=true

In my C# model for the controller:

public DateTime StartDate { get; set; }
user_40561460

A quick googling lead me to this answer, so try

?$filter=CreatedDate lt datetime'2020-06-04T14:27:12.38'

user_85649990
user_32649980

if you are using .NET sdk to generate Storage Table query try using the TableQuery.GenerateFilterConditionForDate version

var timestampToFilter1 = TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.GreaterThanOrEqual, 
                    DateTimeOffset.Parse("2022-06-15T04:32:07.000Z") );
var timestampToFilter2 = TableQuery.GenerateFilterConditionForDate("Timestamp", QueryComparisons.LessThanOrEqual, 
                    DateTimeOffset.Parse("2022-06-15T12:32:07.000Z"));
var timestampToFilter = TableQuery.CombineFilters(timestampToFilter1, TableOperators.And, timestampToFilter2);
user_1845720
Related