Many times I have wanted to ignore the time portion of a datetime field in a database when selecting records for a given date. One rudimentary way would be to store date and time in separate fields. There is another alternative

• Scenario

Logging database with 1 million records

• Configuration

Parallelism: disabled (compare methods more acurately)

Recovery mode: simple (reduces time when adding indexes)

• Scripts

CREATE TABLE [dbo].[Log] (
 [LogID] [int] IDENTITY (1, 1) NOT NULL ,
 [LogDateTime] [smalldatetime] NOT NULL ,
 [Details] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
GO

CREATE  CLUSTERED  INDEX [LogDateTime] ON [dbo].[Log]([LogDateTime]) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Log] ADD
 CONSTRAINT [PK_Log] PRIMARY KEY  NONCLUSTERED
 (
  [LogID]
 )  ON [PRIMARY]
GO

SET NOCOUNT OFF

DECLARE @DateSubtract DECIMAL(9,4)
DECLARE @Counter INT

SELECT @Counter = 0

WHILE @Counter < 1000000
BEGIN
 SELECT @DateSubtract = RAND() * 100000
 INSERT INTO LOG (LogDateTime, Details)
 VALUES (DATEADD(mi, - @DateSubtract, GETDATE()), 'TEST')
 SELECT @Counter = @Counter + 1
END

We will be selecting all records for 7th December 2005 (14188 records)

The first four examples attempt to select records for a given date

1.

SELECT * FROM Log WHERE DATEPART(yy, LogDateTime) = 2005
AND DATEPART(mm, LogDateTime) = 12
AND DATEPART(dd, LogDateTime) = 7

2.

SELECT * FROM Log WHERE DATEADD(d,DATEDIFF(d,0,LogDateTime),0) = DATEADD(d,DATEDIFF(d,0,GETDATE()),-2)

3.

SELECT * FROM Log where DATEDIFF(dd,LogDateTime,GETDATE()-2) = 0   

4.

SELECT * FROM Log WHERE CONVERT(CHAR(8), LogDateTime, 112) = CONVERT(CHAR(8), GETDATE()-2, 112)

The first three have identical performance (4235 logical reads), whereas the 4th is a lot slower (45188 logical reads) - it is unable to use the clustered index on LogDateTime

The next three tests select records where the date is between two dates i.e before 8th December and after 6th December.

I could've used BETWEEN rather than "greater than or equal to" and less than"

5.

SELECT * FROM LOG WHERE LogDateTime >=  CAST(CONVERT(VARCHAR(12),GETDATE() - 2,112) + ' 00:00:00' as DATETIME) AND LogDateTime <  CAST(CONVERT(VARCHAR(12),GETDATE() - 1,112) + ' 00:00:00' as datetime)

6.

SELECT * FROM Log WHERE LogDateTime >= DATEADD(d,DATEDIFF(d,0,GETDATE()),-2)
AND LogDateTime < DATEADD(d,DATEDIFF(d,0,GETDATE()),-1)

7.

SELECT * FROM Log WHERE LogDateTime >= '20051207' AND LogDateTime < '20051208'

These last three methods all cost 63 reads and are by far the best solution. If you have added and index and the index is being used with a scan rather than a seek, or the index is not being used at all, there is a problem!

Execution plan info can be found here

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/optimsql/odp_tun_1_5pde.asp