[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
>