Dates in a WHERE clause - 11-08-2011 , 05:10 PM
Usually, I don't have to worry about the time in a date, but it does
not mean a time isn't there. Usually, the dates are passed into a
script and used in the query. The format is yyyyMMdd.
I like using (1) because it seems to be more efficient (i.e. according
to a query plan).
(2) seems to be inefficient.
(1) WHERE a.date >= '20111108' AND date < dateadd(dd, 1, '20111108')
(2) WHERE CONVERT(VARCHAR, a.date, 112) = '20111108'
Is there a more efficient way to compare dates?
Re: Dates in a WHERE clause - 11-09-2011 , 10:43 AM
On Nov 9, 8:22*am, --CELKO-- <jcelko... (AT) earthlink (DOT) net> wrote:
Re: Dates in a WHERE clause - 11-10-2011 , 09:31 AM
On Nov 9, 10:10*am, 1dg618 <1dg... (AT) gmail (DOT) com> wrote:
Recently SQL and Sybase have introduced DATE and TIME datatypes. But
all databases implemented up to now only had [SMALL]DATETIME
datatypes. So when we needed a Date only or Time only, we had to use
[SMALL]DATETIME; load one component and ignore the other component.
Depending on how good your database and coding standards are, you
would have treated the ignored component consistently or not; and
stored it in a manner that avoids or adds confusion. And then the
code, whether it addresses the ignored component or not, fetches the
If a.Date is DATETIME, and you need only a Date, you loaded only the
Date (and the time would have defaulted to "00:00.000", all code
examining the column should use (eg) CONVERT(CHAR(8), a,Dat, 112).
There is no possibility of inconsistent results due to inconsistent
content of the Time component.
a.Date >= "20111108" relies on automatic conversion, the implicit
"00:00.000", which works fine, but it is vulnerable to inconsistent
content in the Time component.
Both (1) and (2) should produce the same query plan, neither is more
efficient in that sense. (2) is more efficient in that it makes one
comparison instead of two.
And of course, for new code, you can use CONVERT(DATE, a.Date). Which
is (2), using the new datatype.
Re: Dates in a WHERE clause - 11-10-2011 , 12:33 PM
On Nov 10, 9:31*am, Derek Asirvadem <derek.asirva... (AT) gmail (DOT) com> wrote: