WHERE CONVERT(date, some_indexed_datetime_col) = '20110318'

WHERE some_indexed_datetime_col >= '20110318' AND some_indexed_datetime_col < '20110319'

where indexed_datetime_col > @StartDate and indexed_datetime_col < @EndDate

-- Test code

CREATE table T_DATE (DT datetime not null primary key clustered )

-- Load table with 109,573 dates
insert into T_DATE(DT)
select date from dbo.F_TABLE_DATE('19000101','21991231')  ORDER BY date

select * from T_DATE a WHERE CONVERT(date,a.DT) = '20110318'

select * from T_DATE a WHERE a.DT >= '20110318' and a.DT < '20110319'

StmtText
  |--Nested Loops(Inner Join, OUTER REFERENCES:([Expr1005], [Expr1006], [Expr1004]))
       |--Compute Scalar(DEFINE:(([Expr1005],[Expr1006],[Expr1004])=GetRangeThroughConvert(CONVERT_IMPLICIT(date,[@1],0),CONVERT_IMPLICIT(date,[@1],0),(62))))
       |    |--Constant Scan
       |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T_DATE].[PK__T_DATE__3214621725C12FCC] AS [a]), SEEK:([a].[DT] > [Expr1005] AND [a].[DT] < [Expr1006]),  WHERE:(CONVERT(date,[tempdb].[dbo].[T_DATE].[DT] as [a].[DT],0)=CONVERT_IMPLICIT(date,[@1],0)) ORDERED FORWARD)

StmtText
  |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T_DATE].[PK__T_DATE__3214621725C12FCC] AS [a]), SEEK:([a].[DT] >= CONVERT_IMPLICIT(datetime,[@1],0) AND [a].[DT] < CONVERT_IMPLICIT(datetime,[@2],0)) ORDERED FORWARD)

use northwind
go

-- index seek
select orderid, customerid, orderdate from orders 
  where orderdate >= '19970324' and OrderDate < '19970325';

-- index seek, cheaper plan than first one  
select orderid, customerid, orderdate from orders 
  where convert(date, orderdate) = '19970324';

quote:

---

Originally posted by PSamsig

I have been wating for the axamples from the talk to be published, and I can reproduce that date type query optimation is slightly superior to the 'old' method.

use northwind
go

-- index seek
select orderid, customerid, orderdate from orders 
  where orderdate >= '19970324' and OrderDate < '19970325';

-- index seek, cheaper plan than first one  
select orderid, customerid, orderdate from orders 
  where convert(date, orderdate) = '19970324';

Gives a execute ratio of 53% for the first and 47% for the second, it is correct though, that the execution plan for the second one seems more complicated (makes a constant table on which it join through), but unless my SSMS lies, it is faster.

Apart for the oddness of being (slightly) faster, what troubles me is that it is now much more difficult to teach people about what constructs is sargable and what is not.

The session I got this from was by Bob Beauchemin, and I know that in a session I did have time to attend, Itzik Ben-Gan recomended this as well, in case anyone in familiar with either.

-- If you give someone a program, you will frustrate them for a day; if you teach them how to program, you will frustrate them for a lifetime.

---

CREATE TABLE dbo.T_DATE(
	ID int IDENTITY(1,1) NOT NULL,
	DT datetime NOT NULL,
	Filler varchar(50) NULL,
 CONSTRAINT PK_T_DATE PRIMARY KEY CLUSTERED (ID)
)
GO

-- Load table with 109,573 dates
insert into T_DATE(DT)
select date from dbo.F_TABLE_DATE('19000101','21991231')
GO

CREATE NONCLUSTERED INDEX IX_T_DATE_DT ON dbo.T_DATE (DT)
GO

SET STATISTICS IO ON 
GO

select * from T_DATE a WHERE a.DT >= '20110318' and a.DT < '20110319'

select * from T_DATE a WHERE CONVERT(date,a.DT) = '20110318'

Table 'T_DATE'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

-- Waste of time until we are done expanding table
DROP INDEX dbo.T_DATE.IX_T_DATE_DT
GO

-- Make our table a desent size
INSERT T_DATE (DT) SELECT DT FROM T_DATE --  2
INSERT T_DATE (DT) SELECT DT FROM T_DATE --  4
INSERT T_DATE (DT) SELECT DT FROM T_DATE --  8
INSERT T_DATE (DT) SELECT DT FROM T_DATE -- 16
INSERT T_DATE (DT) SELECT DT FROM T_DATE -- 32
INSERT T_DATE (DT) SELECT DT FROM T_DATE -- 64
INSERT T_DATE (DT) SELECT DT FROM T_DATE --128
INSERT T_DATE (DT) SELECT DT FROM T_DATE --256
GO

-- Lets add some random time part
UPDATE T_DATE SET DT = DATEADD(SECOND, abs(checksum(newid())) % (24 * 60 * 60), DT)
GO

-- Building the index again
CREATE NONCLUSTERED INDEX IX_T_DATE_DT ON dbo.T_DATE (DT ASC)
GO

SET STATISTICS IO ON
GO

select * from T_DATE a WHERE a.DT >= '20110318' and a.DT < '20110319'

select * from T_DATE a WHERE CONVERT(date,a.DT) = '20110318'

(256 row(s) affected)
Table 'T_DATE'. Scan count 1, logical reads 797, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(256 row(s) affected)
Table 'T_DATE'. Scan count 1, logical reads 772, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.