[dba-SQLServer] 'Just Date' criteria.

Billy / David 

firstly thanks for responses - as always help of group appreciated.

have gone with using date range 

also you have answered my query in that wrapping the date criteria in
convert function does not stop the index seek.
(I was considering using the convert in the select element of statement as
getdate() for criteria, think this may stop the index seek)

Thanks again.

Regards

john

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Billy
Pang
Sent: 19 May 2005 22:35
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] 'Just Date' criteria.


Hi John:

I'd probably use a date range.  For example, in northwind database, if i 
want to look for all orders with order date on July 8, 1996, I'd use:

select orderid, OrderDate
from orders
where orderdate >= '7/8/1996'
	and orderdate < '7/9/1996';

or...

select orderid, OrderDate
from orders
where orderdate >= convert(smalldatetime,'07/08/1996',101)
	and orderdate < convert(smalldatetime,'07/09/1996',101);

the second select statement is "international date friendly" because it uses

the convert function to explicity define whether the "7" is the day or the 
month.

They both have idential execution plan (except that the first one has to 
convert the text strings  (eg. '7/8/1996') into the datetime datatype before

querying data.

If I go with the datepart method, it would look something like this:

select orderid, OrderDate
from orders
where month(orderdate) = 7
and  day(orderdate) = 8
and year(orderdate) = 1996;

In this example, in my opinion, it is a little bit more harder to read than 
the first two.  Also, it does not use the indexes efficiently because index 
scan is required to get the partial date matches.  In the first two 
examples, index seeks were performed.  Index seeks are better than index 
scans.

Billy

>From: "John Maxwell @ London City" <John.Maxwell2 at ntl.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: "'dba-sqlserver at databaseadvisors.com'" 
><dba-sqlserver at databaseadvisors.com>
>Subject: [dba-SQLServer] 'Just Date' criteria.
>Date: Wed, 18 May 2005 19:55:07 +0100
>
>Hello all,
>
>apologies if basic question but your help is appreciated.
>
>Issue / Problem:
>I need to select data with a particular date value
>but ignore the time contained in the data.
>ie all records from a particular day what ever the time
>
>I can see a number of solutions eg.
>*	Convert date (Guess not always good solution as may not use
>available indexes)
>*	Use range in criteria (Is this efficient)
>*	Use multiple date part functions (Is this efficient  and any
>different from point above)
>
>Any views / advice on what is usually the most efficient method would be
>great
>
>Thanks in advance
>
>john
>
>
>The contents of this email and any attachments are sent for the personal 
>attention
>of the addressee(s) only and may be confidential.  If you are not the 
>intended
>addressee, any use, disclosure or copying of this email and any attachments

>is
>unauthorised - please notify the sender by return and delete the message.  
>Any
>representations or commitments expressed in this email are subject to 
>contract.
>
>ntl Group Limited
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com


The contents of this email and any attachments are sent for the personal attention
of the addressee(s) only and may be confidential.  If you are not the intended
addressee, any use, disclosure or copying of this email and any attachments is
unauthorised - please notify the sender by return and delete the message.  Any
representations or commitments expressed in this email are subject to contract. 
 
ntl Group Limited