OData filter by Date throws "The query specified in the URI is not valid. An identifier was exp-CodePudding

I am using OData to query data from my SQL Db, I am trying to filter data using a Date value. See query below:

http://localhost:4409/OrderMaintenance?$filter=DocType eq 5 and DocState in (0,1,2,3,4,5,6,7) and InvDate eq 2022/02/21

Only the date is the problem in this query because when I remove the date filter the query works fine.

The InvDate is of type:

orderDateFilter: Date = new Date();

I then formatted the date to:

this.orderDateFilter.toLocaleDateString()

which returns date as :

2022/02/21

But I am getting the following error

"The query specified in the URI is not valid. An identifier was expected at position 62."

Stacktrace:

 at Microsoft.OData.UriParser.ExpressionToken.GetIdentifier()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseSegment(QueryToken parent)\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimary()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseInHas()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseUnary()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseMultiplicative()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseAdditive()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()\r\n   at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)\r\n   at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)\r\n   at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()\r\n   at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()\r\n   at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)\r\n   at Microsoft.AspNet.OData.Query.FilterQueryOption.Validate(ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNet.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings)\r\n   at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions)\r\n   at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass0_0.<OnActionExecuted>b__1(ODataQueryContext queryContext)\r\n   at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func`2 modelFunction, IWebApiRequestMessage request, Func`2 createQueryOptionFunction)\r\n   at Microsoft.AspNet.OData.EnableQueryAttribute.OnActionExecuted(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, IWebApiRequestMessage request, Func`2 modelFunction, Func`2 createQueryOptionFunction, Action`1 createResponseAction, Action`3 createErrorAction)

CodePudding user response：

Try to format the odata date filter with dashes instead of backslashes: 2022-02-21 instead fo 2022/02/21. You might also need to add the time portion. Here's an example with time at midnight 2022-02-21T00:00:00Z.

CodePudding user response：

I used :

this.orderDateFilter.toISOString()

to convert my date to:

2022-02-22T09:17:24.822Z

But then I had issues querying, because I wanted to only check the Date excluding the Time. So instead I used:

this.datepipe.transform(this.orderDateFilter.toLocaleDateString(),'yyyy-MM-dd')

Which worked perfectly!