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()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseSegment(QueryToken parent)rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParsePrimary()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseInHas()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseUnary()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseMultiplicative()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseAdditive()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseComparison()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalAnd()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseLogicalOr()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpression()rn at Microsoft.OData.UriParser.UriQueryExpressionParser.ParseExpressionText(String expressionText)rn at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilterImplementation(String filter, ODataUriParserConfiguration configuration, ODataPathInfo odataPathInfo)rn at Microsoft.OData.UriParser.ODataQueryOptionParser.ParseFilter()rn at Microsoft.AspNet.OData.Query.FilterQueryOption.get_FilterClause()rn at Microsoft.AspNet.OData.Query.Validators.FilterQueryValidator.Validate(FilterQueryOption filterQueryOption, ODataValidationSettings settings)rn at Microsoft.AspNet.OData.Query.FilterQueryOption.Validate(ODataValidationSettings validationSettings)rn at Microsoft.AspNet.OData.Query.Validators.ODataQueryValidator.Validate(ODataQueryOptions options, ODataValidationSettings validationSettings)rn at Microsoft.AspNet.OData.Query.ODataQueryOptions.Validate(ODataValidationSettings validationSettings)rn at Microsoft.AspNet.OData.EnableQueryAttribute.ValidateQuery(HttpRequestMessage request, ODataQueryOptions queryOptions)rn at Microsoft.AspNet.OData.EnableQueryAttribute.<>c__DisplayClass0_0.<OnActionExecuted>b__1(ODataQueryContext queryContext)rn at Microsoft.AspNet.OData.EnableQueryAttribute.ExecuteQuery(Object responseValue, IQueryable singleResultCollection, IWebApiActionDescriptor actionDescriptor, Func`2 modelFunction, IWebApiRequestMessage request, Func`2 createQueryOptionFunction)rn 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)
3
Answers
I used :
to convert my date to:
But then I had issues querying, because I wanted to only check the Date excluding the Time. So instead I used:
Which worked perfectly!
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.
If you are using a .NET client (e.g. blazor) to call the API, convert the date to UTC
date.ToUniversalTime()then format it as ISO 8601 by usingdate.ToString("o").This will include the timezone with the Z (Zulu).
Or convert to sortablesafe string then append the Zulu
date.ToString("s") + Z