Eric M Russell (8/20/2014)

---

dirk.dromgoole (8/20/2014)

---

I suppose there are probably better ways to modify the query. Basically, the WHERE clause of the query is:

WHERE (GETDATE() - 1 <= EndDate)

If EndDate has a time of 00:00:00.000 or any time for that matter that is not the end of the day then that whole day is not calculated. EndDate with a time of 00:00:00.000 will be less than GETDATE()-1 so the whole day before will not be accounted for.

If you don't need time portion, then use Date datatype instead of DateTime. If you're stuck with existing columns of type DateTime and just need to restrict data entered, then you can implement a check constraint on the column. Comparing value entered to a re-cast of itself is an easy and reliable way to implement this specific type of check. For example:

create table #t

(

EndDate datetime

constraint ck_EndDate check (EndDate = cast(EndDate as date))

);

insert into #t ( EndDate ) values ('2014/08/20');

(1 row(s) affected)

insert into #t ( EndDate ) values ('2014/08/20 20:59:59.997');

Msg 547, Level 16, State 0, Line 1

The INSERT statement conflicted with the CHECK constraint "ck_EndDate".