Click here to Skip to main content
15,891,513 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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?
Posted
Updated 17-Aug-20 3:06am
Comments
[no name] 17-Aug-20 9:04am    
A workaround: DateTimeField > 10.08.2020 11:55:14.999

1 solution

Would this
var property = Expression.Property(parameter, typeof(DeviceAlert).GetProperty(nameof(DeviceAlert.CreatedTimestamp)));
property = property.AddTicks( - (property.Ticks % TimeSpan.TicksPerSecond));
return equalityOperator switch
{ ... 


You might need (instead of 'var property...' to use 'DateTime property...')

ref : c# - How to truncate milliseconds off of a .NET DateTime[^]
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900