c# - Getting the DateTime for the end of a certain day -

i have following

 datetime today = datetime.today; // or datetime.utcnow.date; 

now, can not in linq-ef because date can not translated sql:

 context.records.where(r => r.creationdate.date == today); 

so, this:

 datetime dayend = today.adddays(1).addmilliseconds(-1);  context.records.where(r => r.creationdate >= today && r.creationdate <= dayend); 

now, there better way getting end of time datetime instead of adding 1 day taking 1 millisecond?

the problem like

datetime today = datetime.today ; context.records.where(r => entityfunctions.truncatetime(r.creationdate) == today) ; 

is underlying sql going this:

select * some_table r convert(date,r.creationdate) == @today 

since column part of expression, sql engine unable make use of covering indices has available. unless you've got other criteria use indices, you're going table scan. , big tables, cause performance issue.

and should avoid obvious

datetime today  = datetime.today ; datetime dayend = today.adddays(1).addmilliseconds(-1); context.records.where(r => r.creationdate >= today && r.creationdate <= dayend) ; 

because of way sql server datetime values work: while count in milliseconds, sql server's 'ticks' occur in 3- , 4-millisecond increments. details, see my answer question how sqldatetime precision reduction. in nutshell:

to conversion in manner in sql server it, following: take milliseconds portion of actual time under consideration, value 0-999, modulo 100. gives low order digit, value 0-9. if current time 23:57:23.559, milliseconds component 559. modulo 100 9.

1. values 0 , 1 "rounded down" 0.
2. values 2, 3 , 4 "rounded down"" 3.
3. values 5, 6, 7 , 8 "rounded down" 7.
4. a value of 9 rounded up 0. has rather unpleasant , nasty side effect: if milliseconds portion of time 999, ticks 1 millisecond. means time 23:59:59.999 rounded the next day. conversion of '31 dec 2010 23:59:59.999' yields datetime value of...1 january 2011 00:00:00.000.

note smalldatetime exhibits kind of behavior. datetime2 exempt.

so...

unless you're careful, it's easy drop things on floor have been timestamped right @ end-of-day (don't ask how know this).

you're better off doing like

datetime today    = datetime.today ; datetime tomorrow = today.adddays(1); context.records.where(r => r.creationdate >= today && r.creationdate < tomorrow) ; 

which should translate sql looks like

select * some_table r r.creationdate >= @today   , r.creationdate <  @tomorrow 

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