Dates in a WHERE clause

[1dg618]

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')

or

(2) WHERE CONVERT(VARCHAR, a.date, 112) = '20111108'

Is there a more efficient way to compare dates?

[--CELKO--]

Why not use the DATE data type in teh DDL or, if you must, use CAST
(my_date AS DATE)?

[1dg618]

Why not use the DATE data type  in teh DDL  or, if you must, use CAST
(my_date AS DATE)?

I'll try it. Thank you. I didn't think of the CAST.

[Derek Asirvadem]

Post by 1dg618
Is there a more efficient way to compare dates?

Yes, but first you need to understand the problem precisely.

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
data consistently.

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.

Regards
Derek

[1dg618]

Post by Derek Asirvadem

Post by 1dg618
Is there a more efficient way to compare dates?

Yes, but first you need to understand the problem precisely.
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
data consistently.
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.
Regards
Derek

Awesome. Thank you!

[Derek Asirvadem]

On Nov 11, 5:33 am, 1dg618 <***@gmail.com> wrote:

My pleasure.