how to cast with eq in odata filtering?

Question

I want to set filters in odata queries with user interaction. When the user selects a data attribute I already know its type. For instance if the user want to filter with SALE_PRICE equal to some number I already know that the selected attribute is of type Edm.Decimal. So I tried to use this fact and build the query with the cast operation. For instance, to get the data where the SALE_PRICE is equal to 323.7 I create the following URI:

analyticView?$select=AMOUNT_SOLD,FAMILY_NAME&$filter=SALE_PRICE+eq+(cast(323.7,'Edm.Decimal'))&$format=json

but I am getting an error message saying:

"No property 'cast' exists in type 

I also tried this on the serivces.odata.org API, and it doesn't seem to work

http://services.odata.org/V3/Northwind/Northwind.svc/Orders?$select=Freight,OrderID&$filter=OrderDate+eq+(cast(1996-07-05T00:00:00,'Edm.DateTime'))&$top=5&$format=json

could you please check what's wrong

Answer 1

Why do you need the cast? You could try this query instead,

analyticView?$select=AMOUNT_SOLD,FAMILY_NAME&$filter=SALE_PRICE eq 323.7M&$format=json

You can represent decimals by suffixing them with m|M like 3.27M

Answer 2

"1996-07-05T00:00:00" is not a well-formatted datetime constant, it should be "datetime'1996-07-05T00:00:00'" then this query works: http://services.odata.org/V3/Northwind/Northwind.svc/Orders?$select=Freight,OrderID&$filter=OrderDate+eq+(cast(datetime'1996-07-05T00:00:00','Edm.DateTime'))&$top=5&$format=json

Not sure why you got error out of your analyticView request. it will be good if you can share more details about the model and error message.

Answer 3

To resolve this issue I created this handler

//How to use 
 var test = odataHanlder.odataFilter();

//how to extend with new handler 
test.addFilterHandler("Edm.Int16", function(_value){
return _value;
});

//how to prepare filter on the fly 
console.log(test.prepareFilter("Edm.DateTime","1996-07-04T00:00:00"));
//result >> datetime'1996-07-04T00:00:00' 

you can find a more detailed description here

feedback is welcome!