I'm using radzen to build a filter and apply it to a view that acts as my data set. Most everything works fine however when I try to filter by DateTime I get an error
Conversion failed when converting date and/or time from character string.
This is coming from the end resulting query which directly below. Which has the time portion which I don't need/can't use that way.
SELECT
[a].[SellBy]
, [a].[ProductName]
FROM
[dbo].[Products] AS [a]
WHERE
[a].[SellBy] = '2023-02-05T00:00:00.0000000'
In my code to convert the OData filter I have
if (IsNullableType(left.Type) && !IsNullableType(right.Type))
{
if(right is ConstantExpression offset)
{
if(offset.Value is DateTimeOffset dateTimeOffset)
{
DateTime? dateTime = dateTimeOffset.DateTime;
right = ConstantExpression.Constant(dateTime, typeof(DateTime?));
}
}
}
Which gets me the below expression I pass directly to an entity framework where clause
(Products.SellBy == 9/6/2023 12:00:00 AM)
Then generates the problematic where clause
[a].[SellBy] = '2023-02-05T00:00:00.0000000'
OData Query
SellBy eq 2023-09-06T00:00:00.000Z
My question is how can I parse the above OData filter into a way that will allow me do run = <> < <= >= queries for dates. I'm close however my resulting query from the expression I'm building includes the time portion which I can't use.
There is more to the code including a section where I parse the OData query into a OData expression using an EDM which I'm not super familiar with. I left that out for brevity but I can post it if need be. I didn't include it because I think it's working fine. I'm able to run bool/text predicates using what I have so far.
'2023-02-05T00:00:00.0000000') of datetime works well with columns data typedatetime2. In your case, conversion is successful with sting'2023-02-05T00:00:00.000'IQueryable. Have you tried that?