Dealing with SQL server datetime edge cases

Question

I have the following data in a SQL Server 2000 table:

Dates
-----------------------
2012-05-04 01:23:45.678
2012-05-05 01:23:45.678
2012-05-06 01:23:45.678

Suppose GETDATE() returns:

2012-05-05 12:34:56.789

I need the most efficient (and elegant) query that returns rows #2 and #3 from above, the criteria being:

date portion of Dates >= date portion of GETDATE()

Correlated to: http://stackoverflow.com/questions/353014/convert-sql-server-datetime-fields-to-compare-date-parts-only-with-indexed-look — David Brabant, May 05 '12 at 08:13
@Oded: `WHERE CONVERT( VARCHAR, Dates, 101 ) >= GETDATE( ) - 1` (i) it does not use any indexes (ii) it fails on edge case when time portion for `GETDATE()` is exactly `00:00:00`. — Salman A, May 05 '12 at 08:19
Start off by "caching" `GETDATE()` (or the exact value you need - without the time portion) in a local variable. — Oded, May 05 '12 at 08:21
And you only need to compare the _whole_ date to the current date in order to get that value. Converting to a VARCHAR kills indexing - it is not sargable. — Oded, May 05 '12 at 08:25
@Oded: I convert it to a varchar to eliminate the time portion (it is the only way I know). — Salman A, May 05 '12 at 08:57
@SalmanA - It is not a good way when used in a `WHERE` clause. — Oded, May 05 '12 at 08:58

score 4 · Accepted Answer · edited May 23 '17 at 11:44

4

DECLARE @Now DATETIME
SELECT @Now = DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 0)

SELECT ...
WHERE Dates >= @Now

Adapted from this and this.

edited May 23 '17 at 11:44

Community

answered May 05 '12 at 08:32

Oded

1 Answers1