Search code examples
c#linqdate-rangebetween

Get data in a specific date for a time range with a minute difference

I have the following datetime range:

22/07/2021 07:00:52 (start date) 22/07/2021 07:01:00 (end date)

I want all the data between this range including the right hand boundary.

My linq to SQL query:

Select * from tableName where CreatedAt >= startDate and CreateAt <= endDate

The issue here with the query is I am unable to get the record whose CreatedAt= 22/07/2021 07:01:00 (end date). If I do AddMinutes(1) to the endDate, I get the required datas but I also get the data greater than endDate. For example data having following Created At dates

22/07/2021 07:01:15 22/07/2021 07:01:20

which is faulty.

How do I include the right boundary in the range comparison for datetime?

I have tried removing the miliseconds from the database record as:

select *  from Custom_Devices  
where
DATEADD(ms, -DATEPART(ms, CreatedAt), CreatedAt) >= '2019-07-29
19:00:00' 
and 
DATEADD(ms, -DATEPART(ms, CreatedAt), CreatedAt) <= 
'2019-07-29 19:01:00'

Attaching a screenshot of the output: enter image description here

Expected output: enter image description here

Update: CreatedAt column is a datetime2 column in database.

Solution

  • f I do AddMinutes(1) to the endDate

    Not sure why you'd add a whole minute when the problem is caused by additional milliseconds and can be solved by going up a second..

    Do you actually want to keep the milliseconds on the dates? Is it any use to you to know that the event occurred at 12:34.56.789 rather than 12:34:56? If not, make the column a datetime2(0) to discard the milliseconds permanently

    Otherwise I recommend the route several other people are also advocating and do your LINQ like

    context.Whatever.Where(x => x.CreatedAt >= startDate && x.CreatedAt < endDate.AddSeconds(1))

    The "less than" is vital. A "less than 12:34:57" will get 12:34:56.999999... which appears to be what you want with your "less than or equal to 12:34:56"

    If you're struggling to understand why milliseconds cause a problem, think of dates like numbers - if you have a number 1.222222 and you ask the db for "less than or equal to 1.2" the DB doesn't auto-round the 1.222222 down to 1 decimal place and then go "oh it's equal to 1.2" and return it. It just goes "1.222222 is not less than or equal to 1.2, don't return it"

    Over time for question like this you'll always get someone who says "just cast it to.." or "run this to calculate a new date to remove the milliseconds..." - don't; if you write a query that manipulates the table data, that manipulation has to be performed every time the data is queried. It typically kills the ability for the db to use an index on the column too (the db will probably switch to running the manipulation on every value in the index, every time) which means the query is massively more resource intensive.

    Always consider manipulating table data in a where clause as an absolute last resort. If there is no other way and the query will be heavily used, look at adding some kind of calculated column to the table and index it so that, conceptually, the result of the manipulation you're carrying out is done once and an index on it can be used