What's the easiest way to ignore milliseconds when filtering over DateTime (datetime2) property in SQL Db?
What I have tried:
What I have is a DateTime filter like this:
<pre>
var parameter = Expression.Parameter(typeof(DeviceAlert), "x");
var predicate = Expression.Constant(request.AlertCreatedFilter.Value, typeof(DateTime));
var filterBinaryExpression = EvaluateEqualityOperator((EqualityOperator)request.EqualityOperator, parameter, predicate);
var toLambdaExpression = Expression.Lambda<Func<DeviceAlert, bool>>(filterBinaryExpression, parameter);
query = query.Where(toLambdaExpression);
private BinaryExpression EvaluateEqualityOperator(EqualityOperator equalityOperator, Expression parameter, Expression predicate)
{
var property = Expression.Property(parameter, typeof(DeviceAlert).GetProperty(nameof(DeviceAlert.CreatedTimestamp)));
return equalityOperator switch
{
EqualityOperator.Equal => Expression.Equal(property, predicate),
EqualityOperator.GreaterThan => Expression.GreaterThan(property, predicate),
EqualityOperator.GreaterThanOrEqual => Expression.GreaterThanOrEqual(property, predicate),
EqualityOperator.LessThan => Expression.LessThan(property, predicate),
EqualityOperator.LessThanOrEqual => Expression.LessThanOrEqual(property, predicate),
_ => throw new ArgumentOutOfRangeException("Equality operator not found."),
};
}
Everything works fine except that I need to ignore milliseconds in DateTime filter queries. So e.g.
DateTimeField >= 10.08.2020 11:55:15
should return all values for that datetime, no matter what milliseconds are.
Is it doable without converting to string and comparing with Contains?