Have Geek Brain

Fastest way to check date range

I am storing events in SQLServer 2005 where the time when the event happened is important and should be stored in date. What's the fastest way to write a date range check in a where clause so that everything is selected on that day?

Currently, when @DateStart and @DateEnd are passed, I set @DateStart to midnight and set @DateEnd to last minute before midnight as the very first thing to catch every possible event on that 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,

+1


a source to share


6 answers


You can always use the alternative syntax WHERE EventDate BETWEEN @DateStart AND @DateEnd



+7


a source


Your where clause will look like this:

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 your whole IF statement will handle null parameters (i.e. IF @DateEnd IS NULL THEN SET @DateEnd = @DateStart)

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

+2


a source


fastest way to truncate a date prior to midnight:

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

next midnight:

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

You can also wrap this as an inline UDF:

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

+1


a source


Try the following:

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

EDIT To refer to Tom H's comment: I've never had any luck with indexes on date fields; what was always best for me was the extra integer columns to handle the year and day values ​​of the year and index them.

0


a source


AlexK is probably the best idea. The only thing I'm going to worry about is executing function usage in the predicate.

You should add a column named searchdate or something similar to the table to contain the date without time. I would also suggest indexing this column, especially if you will be looking for column data many times.

When you query this column, you won't have any scalar functions in your SQL to rob your indexes of their performance.

Con ... well, extra storage space, time during insert to write data (not much here).

SQL Server 2008 has better support for date-only data types. You can also register on it.

0


a source


I think this T-SQL is equivalent to your code:

    - 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 function CONVERT

will handle NULLS, so there is no need for a separate test for the NULL value (unless, of course, you do special processing other than what you show and pass NULL values ​​before the predicate is requested (i.e. the WHERE clause). Or perhaps you are expecting many arguments to be NULL and you want to avoid the overhead of CONVERT calls.

However, I agree with Tom X's recommendation and avoid messing around with subtracting milliseconds, and instead set @DateEnd until midnight the next day, for example.

    - 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 the range test like this:

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

You can avoid seperate SET statements and wrap the expressions right into the query, but I don't expect this to improve performance or make the SQL statement more difficult to read, you definitely want to keep the comments ...

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


a source






More articles:


All Articles