0

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.

2
  • 1
    This format ('2023-02-05T00:00:00.0000000') of datetime works well with columns data type datetime2. In your case, conversion is successful with sting '2023-02-05T00:00:00.000' Commented Sep 25, 2023 at 6:45
  • OData library itself can apply filer on IQueryable. Have you tried that? Commented Sep 25, 2023 at 15:15

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.