Fastest way to check date range...

Question

I store events in SQLServer 2005 where the time the event occured is important and must be stored in the datebase. What is the fastest way to write the date range check in the where clause to ensure everything on that day is selected?

Currently when @DateStart and @DateEnd are passed in I set @DateStart to midnight and set @DateEnd to the last instant before midnight as the very first thing to catch every possible event on the day.

IF (@DateStart IS NOT NULL)
BEGIN
    SET @DateStart = CAST (
           ( CAST (DATEPART (yyyy,@DateStart) AS NVARCHAR(4)) +'/'+
            CAST (DATEPART (mm,@DateStart) AS NVARCHAR(2)) +'/'+
            CAST (DATEPART (dd,@DateStart) AS NVARCHAR(2)) +' '+
            '00:00:00.000'
           )
          AS DATETIME)
END

IF (@DateEnd IS NOT NULL)
BEGIN
    SET @DateEnd = CAST (
          ( CAST (DATEPART (yyyy,@DateEnd) AS NVARCHAR(4)) +'/'+
           CAST (DATEPART (mm,@DateEnd) AS NVARCHAR(2)) +'/'+
           CAST (DATEPART (dd,@DateEnd) AS NVARCHAR(2)) +' '+
           '23:59:59.997'
          )
          AS DATETIME
         )
END

So the where clause is very easy to read:

WHERE ( EventDate >= @DateStart AND EventDate <= @DateEnd )

Thanks,

Answer 1

You could always use the alternate syntax of WHERE EventDate BETWEEN @DateStart AND @DateEnd

Answer 2

Try this:

WHERE DATEPART(yyyy, EventDate) = DATEPART(yyyy, getdate()) 
      AND DATEPART(dy, EventDate) = DATEPART(dy, getdate())  --day of year

EDIT To address Tom H's comment: I've never had any luck with indexes on date fields; what has always worked better for me was extra integer columns to handle the year and day of year values and indexed those instead.

Answer 3

the fastest way to truncate a date, previous midnight:

DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate), '19010101')

next midnight:

DATEADD(day, DATEDIFF(day, '19010101', LastModifiedDate)+1, '19010101')

You can also wrap this up as an inline UDF:

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-with-cross-apply.aspx

Answer 4

AlexK's is probably the best idea all around. The only thing I would worry about is performance of using functions in the predicate.

You should consider adding a column called searchdate, or something similar, to your table to contain the date without the time. I'd also suggest indexing this column, especially if you're going to be searching against the column's data a lot.

When you query on this column you will not have any scalar functions in your SQL to rob your indexes of their performance.

The con... well, additional storage space, time during insert to write the data (not much here though).

SQL Server 2008 has better support for date only data types. You could also check in on it.

Answer 5

Your where clause would look like;

WHERE DateCol >= DATEADD(dd, DATEDIFF(dd, 0, @DateStart), 0) --Midnight on the Start date
    AND DateCol < DATEADD(dd, DATEDIFF(dd, 0, @DateEnd + 1), 0) --Midnight of the day after End date

and all your IF statement would do is handle null parameters (i.e. IF @DateEnd IS NULL THEN SET @DateEnd = @DateStart)

You probably want to Index on DATEADD(dd, DATEDIFF(dd, 0, DateCol), 0) if your table is large.

Answer 6

I think this T-SQL is equivalent to the code you have:

    -- set time portion of @DateStart back to midnight
    SET @DateStart = CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateStart,20),20)

    -- advance time portion of @DateEnd to last instant before next midnight
    SET @DateEnd = CONVERT(DATETIME,CONVERT(VARCHAR(11),@DateEnd,20)+'23:59:59.997',21)

The CONVERT function will handle NULLS, so there's no need for a separate test for a NULL value (unless, of course, you are doing some special handling other than what you are showing, and are not passing the NULL values through to query predicate (i.e. WHERE clause). Or, perhaps you are expecting a lot of the arguments to be NULL, and you want to avoid the overhead of the calls to CONVERT.

However, I concur with Tom H.'s recommendation, and avoid messing with subtracting milliseconds, and instead set the @DateEnd to midnight of the following day e.g.

    -- advance @DateEnd to midnight of following day
    SET @DateEnd = DATEADD(day,1,CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateEnd,20),20))

and change the predicate to do a range test like this:

WHERE (EventDate >= @DateStart AND EventDate < @DateEnd)

---

You can avoid the separate SET statements, and move the expressions straight into the query, but I don't expect that will improve performance any, and make the SQL statement harder to read, you'd definitely want to keep the comments ...

WHERE (EventDate >= CONVERT(DATETIME,CONVERT(VARCHAR(10),@DateStart,20),20) AND EventDate

Answer 7

I have a where clause as:

WHERE CAST(CONVERT(DATETIME,A.CLACTDATE,'+ @SQLDateFormat +') AS CHAR(11)) BETWEEN CONVERT(DATETIME,'+ @CallFromDate +','+ @SQLDateFormat +') AND CONVERT(DATETIME,'+ @CallToDate +','+ @SQLDateFormat +')

Please tell me as how to rewrite this to improve performnce....

Regards, Soni