[SOLVED] OData filter by Date throws "The query specified in the URI is not valid. An identifier was expected at position 62"

Issue

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%20eq%205%20and%20DocState%20in%20(0,1,2,3,4,5,6,7)%20and%20InvDate%20eq%202022/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)

Solution

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!

Answered By – Sigcino

Answer Checked By – Katrina (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published. Required fields are marked *