Yes. But, the trick is to restate the condition so that the DateAdd is operating on the local variable rather than the column. Does the search date have a time component? Or, is it set to midnight? Table column have any time?

The more complex situation might be if the search date and the date column both have a time component. And, you want to find all rows that fall on the same day as the search date.

--Initial value is whatever...
SET @SearchDate = '2006-03-30 14:17:03.997'
--Clean it (i.e., strip the time)...
SELECT @SearchStart= Convert( varchar, @SearchDate, 112 )
-- Now the value will be '2006-03-30 00:00:00.000'
--
-- Set the end timepoint to the last time in the day
SELECT @SearchEnd = DateAdd( ms, -3, DateAdd( dd, 1, @SearchStart ) )
-- That will be '2006-03-30 23:59:59.997' because the next datetime value will be midnight of day + 1

SELECT *
FROM myTable
WHERE tableDate BETWEEN @SearchStart AND @SearchEnd

Jay



From: Ken Ross
Sent: Thu 30-Mar-06 16:33
To: LazyDBA Discussion
Subject: RE: Optimizing date searches


Hmm... Ok, but in a case where the underlying column type is a datetime
but I'm really just looking for all records for a particular date (or
range) I'd still need to use the DateAdd trick wouldn't I?

-----Original Message-----
From: Jay Butler
[mailto:mssqldba-ezmlmshield-x73815591.[Email address protected]
Sent: Thursday, March 30, 2006 4:24 PM
To: LazyDBA Discussion
Subject: RE: Optimizing date searches

Internally, SQL Server probably processes those two constructs
identically (with the exception of excluding the begin point in your
">=").

But, neither of those will use an index on your table column if one
exists. You need to state the condition such that you reference the
column rather than some operation (like DateAdd) on the column.


-----Original Message-----
From: Ken Ross [mailto:mssqldba-ezmlmshield-x99131614.[Email address
protected]
Sent: Thu 30-Mar-06 15:10
To: LazyDBA Discussion
Subject: Optimizing date searches

O guru's of GUID, O sultans of SQL, O great wizards of WHERE...

When doing a search filtering by dates, is there any
advantage/disadvantage to doing:

... WHERE @mySearchDate >= Table.Date_Field AND @mySearcDAte <
DateAdd(dd, 1, Table.Date_Field)

VS.

... WHERE @mySearchDate BETWEEN Table.Date_Field AND DateAdd(dd, 1,
Table.Date_Field)


Thanks!!


Ken



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html




---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY Website :
http://www.LazyDBA.com To unsubscribe:
http://www.lazydba.com/unsubscribe.html



---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
Website : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html

MS Sql Server LazyDBA home page