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

This page was last updated on May 01, 2006 04:28 PM.