Getting the DateTime for the end of a certain day

Question

I have the following

 DateTime today = DateTime.Today; // or DateTime.UtcNow.Date;

Now, I can not do something like this in Linq-EF because Date can not be translated to SQL:

 context.Records.Where(r => r.CreationDate.Date == today);

So, I always do something like this:

 DateTime dayEnd = today.AddDays(1).AddMilliseconds(-1);
 context.Records.Where(r => r.CreationDate >= today && r.CreationDate <= dayEnd);

Now, is there a better way for getting the end of time DateTime instead of adding one day then taking one millisecond?

score 5 · Answer 1 · answered Jan 31 '14 at 22:55

Use EntityFunctions.TruncateTime instead:

context.Records.Where(r => EntityFunctions.TruncateTime(r.CreationDate) == today);

You can also skip AddSeconds(-1) by changing condition to <:

DateTime tomorrow = today.AddDays(1);
context.Records.Where(r => r.CreationDate >= today && r.CreationDate < tomorrow);

score 1 · Answer 2 · edited Jan 31 '14 at 22:58

1

You can use EntityFunctions.TruncateTime:

context.Records.Where(r => EntityFunctions.TruncateTime(r.CreationDate) == today);

edited Jan 31 '14 at 22:58

MarcinJuraszek

124,003
15
196
263

answered Jan 31 '14 at 22:55

Sergey Berezovskiy

232,247
41
429
459

Racing with Marcin ;) – Evan L Jan 31 '14 at 22:55
@EvanL 15 seconds behind because of formatting link :) – Sergey Berezovskiy Jan 31 '14 at 22:56
@MIH better way to check day end is comparing with next day start – Sergey Berezovskiy Jan 31 '14 at 22:57
1

@SergeyBerezovskiy thanks! I guess I have to use the `<` operator instead of `<=`.. – Nean Der Thal Jan 31 '14 at 22:58

Joe Brunscheon · Answer 3 · 2014-01-31T23:12:53.213

1

Use SqlFunctions for "dayofyear" and "year":

context.Records.Where(r => 
SqlFunctions.DatePart("dayofyear", r.CreationDate) == DateTime.Now.DayOfYear 
&& SqlFunctions.DatePart("year", r.CreationDate) == DateTime.Now.Year);

edited Jan 31 '14 at 23:12

answered Jan 31 '14 at 22:59

Joe Brunscheon

1,949
20
21

It's about LINQ to Entities? – MarcinJuraszek Jan 31 '14 at 23:00
In that case, you would just use the SqlFunctions: `SqlFunctions.DatePart("dayofyear", r.CreationDate) == DateTime.Now.DayOfYear` – Joe Brunscheon Jan 31 '14 at 23:02
1

This will not work, for example, if I have this date ('01JAN2014') it will return all records created in first of JAN, regardless of the year.. – Nean Der Thal Jan 31 '14 at 23:03
Easy enough to add a fix for the year part of the query. – Joe Brunscheon Jan 31 '14 at 23:04

score 1 · Accepted Answer · edited May 23 '17 at 12:31

The problem with something like

DateTime today = DateTime.Today ;
Context.Records.Where(r => EntityFunctions.TruncateTime(r.CreationDate) == today) ;

is that underlying SQL is likely going to look something like this:

select *
from some_table r
where convert(date,r.CreationDate) == @today

Since the column is now a part of an expression, the SQL engine is unable to make use of any covering indices it has available. Unless you've got other criteria that will use indices, you're going to table scan. And with big tables, that will cause performance issue.

And you should avoid the obvious

DateTime today  = DateTime.Today ;
DateTime dayEnd = today.AddDays(1).AddMilliseconds(-1);
context.Records.Where(r => r.CreationDate >= today && r.CreationDate <= dayEnd) ;

because of the way SQL Server datetime values work: while they count in milliseconds, SQL Server's 'ticks' occur in 3- and 4-millisecond increments. For details, see my answer to the question How does SqlDateTime do its precision reduction. But in a nutshell:

To do the conversion in the manner in which SQL Server does it, do the following: Take the milliseconds portion of the actual time under consideration, a value from 0-999, modulo 100. That gives you the low order digit, a value from 0-9. So if the current time is 23:57:23.559, the milliseconds component is 559. Modulo 100 you get 9.

Values 0 and 1 are "rounded down" to 0.

Values 2, 3 and 4 are "rounded down"" to 3.

Values 5, 6, 7 and 8 are "rounded down" to 7.

A value of 9 is rounded UP to 0. This has a rather unpleasant and nasty side effect: if the milliseconds portion of the time is 999, it ticks up 1 millisecond. This means that the time 23:59:59.999 is rounded up to THE NEXT DAY. So conversion of '31 Dec 2010 23:59:59.999' yields a datetime value of...1 January 2011 00:00:00.000.

Note that smalldatetime also exhibits this kind of behavior. Only datetime2 is exempt.

So...

Unless you're careful, it's really easy to drop things on the floor that have been timestamped right at end-of-day (don't ask how I know this).

You're better off doing something like

DateTime today    = DateTime.Today ;
DateTime tomorrow = today.AddDays(1);
context.Records.Where(r => r.CreationDate >= today && r.CreationDate < tomorrow) ;

which should translate into SQL that looks something like

select *
from some_table r
where r.CreationDate >= @today
  and r.CreationDate <  @tomorrow

and will allow the optimizer to make use of indices on the date/time column.

@MIH: no. While SQL Server's `datetime` measure in milliseconds, it ticks its date/time clock in 3- and 4-millisecond increments. And the way it does so means that a `System.DateTime` value of `2013-12-31 23:59:59.999` is converted to the SQL Server `datetime` value `2014-01-01 00:00:00.000`. See my amended answer for details. — Nicholas Carey, Feb 01 '14 at 00:47

Getting the DateTime for the end of a certain day

4 Answers4