Binaryworld - How to form effective date only search condition. ... [ SQL Server -> T-SQL ]

This script shows a technique to perform effective date searches especially when searching on an indexed column.

CREATE TABLE #Dates (
    DateCol datetime
);
CREATE CLUSTERED INDEX IX_Dates ON #Dates( DateCol );
INSERT INTO #Dates VALUES( '2000-09-26 10:00:18' );
INSERT INTO #Dates VALUES( '2000-09-26 01:00:23' );
INSERT INTO #Dates VALUES( '2000-09-28 18:20:09' );
INSERT INTO #Dates VALUES( '2000-09-28 03:35:58' );
INSERT INTO #Dates VALUES( '2000-10-20 10:00:47' );
INSERT INTO #Dates VALUES( '2000-10-09 11:41:28' );
INSERT INTO #Dates VALUES( '2000-10-02 06:18:01' );
INSERT INTO #Dates VALUES( '2000-10-06 09:33:10' );
INSERT INTO #Dates VALUES( '2000-10-06 12:45:00' );
SELECT * FROM #Dates;
/*
DateCol
------------------------------------------------------
2000-09-26 01:00:23.000
2000-09-26 10:00:18.000
2000-09-28 03:35:58.000
2000-09-28 18:20:09.000
2000-10-02 06:18:01.000
2000-10-06 09:33:10.000
2000-10-06 12:45:00.000
2000-10-09 11:41:28.000
2000-10-20 10:00:47.000
*/
GO
/*
    To get all dates between two given values. Observe the cost of both approaches using
    the show estimated plan output. You can notice that the 1st method forms about
    85.46% of the cost ( relative to the batch ) & the 2nd method forms about
    14.54% of the cost ( relative to the batch ).
*/
DECLARE @Start datetime , @End datetime;
SELECT @Start = '2000-09-26' , @End = '2000-10-08';
-- Usual way of doing the search
SELECT * FROM #Dates
WHERE CONVERT( datetime , CONVERT( varchar , DateCol , 112 ) ) BETWEEN @Start And @End;
-- Showplan for the search shows that the index is not used. This is because of the use
-- of the CONVERT function on the indexed column.
/*
StmtText
--------------------------------------------------------------------------------------------------------------------
|--Filter(WHERE:(Convert(Convert([#Dates].[DateCol]))>=[@Start] AND Convert(Convert([#Dates].[DateCol]))<=[@End]))
    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[#Dates_____________________________00000000000D].[IX_Dates]))
*/
/*
DateCol
------------------------------------------------------
2000-09-26 01:00:23.000
2000-09-26 10:00:18.000
2000-09-28 03:35:58.000
2000-09-28 18:20:09.000
2000-10-02 06:18:01.000
2000-10-06 09:33:10.000
2000-10-06 12:45:00.000
*/

/*
    The same search can be done efficiently by manipulating the date portions of the
    Start & End Dates to take care of time values in the date column. So the time
    portion of Start & End date should always start from '12:00 AM'. One day is added to
    get the End date & that is used as the limiting range for the search.
    This will ensure that any time values for the boundary dates are automatically included.
*/
SELECT @Start = convert( varchar , @Start , 112 ) ,
    @End = DATEADD( day , 1 , convert( varchar , @End , 112 ) ) ;
SELECT @Start AS SearchStart , @End AS SearchEnd;
/*
SearchStart       SearchEnd
----------------------- ------------------------------------------------------
2000-09-26 00:00:00.000 2000-10-09 00:00:00.000
*/

SELECT * FROM #Dates
WHERE DateCol >= @Start And DateCol < @End;
-- Showplan text indicates that index seek is performed in this case. This method is very
-- efficient on large tables & the overhead is only in setting up the search values appropriately.
/*
StmtText
----------------------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[#Dates_00000000000D].[IX_Dates]),
             SEEK:([#Dates].[DateCol] >= [@Start] AND
                [#Dates].[DateCol] < [@End]) ORDERED FORWARD)
*/
/*
DateCol
------------------------------------------------------
2000-09-26 01:00:23.000
2000-09-26 10:00:18.000
2000-09-28 03:35:58.000
2000-09-28 18:20:09.000
2000-10-02 06:18:01.000
2000-10-06 09:33:10.000
2000-10-06 12:45:00.000
*/
GO
DROP TABLE #Dates;
GO