Servicebus topic SQL filters with datetime

Question

I'm sending messages to Servicebus topic with c# app and I'm setting application properties as follows.
The documentation mentioned, that the types for properties is inferred and for datetime - the values need to be in RFC2616 format.

var validFromDate = DateTimeOffset.TryParse(validFrom, out var fromOffset) ? fromOffset.DateTime.ToString("r") : null;
var validToDate = DateTimeOffset.TryParse(validTo, out var toOffset) ? toOffset.DateTime.ToString("r") : null;
var eventDate = DateTimeOffset.TryParse(aEvent.EventTime.ToString(CultureInfo.InvariantCulture), out var eventDateTimeOffset) ? eventDateTimeOffset.DateTime.ToString("r") : null;

ServiceBusMessage message = new ServiceBusMessage(JsonSerializer.Serialize(aEvent));                 message.ApplicationProperties.Add("action_type", aEvent.EventType);                 message.ApplicationProperties.Add("event_time", aEvent.EventTime);                 message.ApplicationProperties.Add("author", aEvent.Author);                 message.ApplicationProperties.Add("o_valid_from", validFromDate);

And these seem to work fine this far:

I'm trying to apply an filter to a topic subscription:

With just the type and product_family checks, this filter works fine.
But the datetime comparison seems to fail and never gets send to the subscription.

How to make this work?

My full filter would eventually look like this:

type = 'delayed' 
AND product_family IN ('formula', 'production_formula') 
AND (o_valid_from IS NOT NULL AND o_valid_from <= event_date)
AND (o_valid_to IS NULL OR o_valid_to >= event_date)

Answer 1

@Jarno Thanks for reaching out.

If you have filter for date and time comparison, then you need to create the filter using .NET

If you create the filter using service bus explore or portal or any other way, then it will treat it as a string comparison.

I am sharing the previous discussion on how you can use the SDK to create the filter. Similarly, you can do the same with the new SDK's or the management library. In case if you are still facing the issue then please let me know.

https://learn.microsoft.com/en-us/archive/blogs/servicebus/how-to-use-datetime-in-a-sqlfilter-in-dotnet

https://techcommunity.microsoft.com/t5/messaging-on-azure-blog/how-to-use-datetime-in-a-sqlfilter-with-net/ba-p/370690

https://stackoverflow.com/questions/37705333/azure-service-bus-using-datetime-in-topic-subscription-sqlfilter-in-net

Please 'Accept Answer' if it helped so that it can help others in the community looking for help on similar topics.