DateTime Field Search In Table

Question

DateTime Field Search In Table

amitabhssinha

SSC-Addicted

Points: 481
More actions
January 19, 2010 at 12:41 am

#219398

Hi There All,
I have a table with DateTime field with millions of record. Now I want to retrieve records based on search criteria of DateTime field.
I have written a query, would this be helpful, kindly suggest me some. The Query is as follows:
SELECT * FROM DATE_SAMPLE
WHERE SAMPLE_DATE >= '2003-04-09'
AND SAMPLE_DATE <'2003-04-10'
(OR)
this would be better than above,
SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09'
AND '2003-04-10'
Please suggest.

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

Notice that both queries are not the same. The second query that uses the between operator will also return records where column SAMPLE_DATE has the date 2003-04-10 in it because the between operator includes the minimum and maximum boundaries in the result set. However the first query will not return those records.

As for your original question – If you are talking about performance, there shouldn’t be difference between queries that use between operator or queries that use the <= and >= operators (assuming that they are using the same values as minimum and maximum boundaries). Both versions should use the same query plan. Personally I prefer using the operators <= and >= because this way the criteria is very clear. If I use the between operator, it is not always clear to everyone if the minimum and maximum boundaries are included in the criteria or not.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 2

Thank u Adi for your suggestion.

I think the option you gave is good, using a >=and<= operator instead of BETWEEN is much better to know the boudaries of condition.

Thanks a lot.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 3

Just a couple of questions...

- how many rows are stored on DATE_SAMPLE table?

- how many rows is supposed to retrieve a tipical query on SAMPLE_DATE column?

- Are queries range-filtering on SAMPLE_DATE column the most typical queries on DATE_SAMPLE table?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 4

Answers to ur queries:

1. In the table, there are millions of records stored and are keep on increasing frequently.

2. As per based on date search criteria the records has to be filtered out.

Your suggestion please.

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 5

amitabhssinha (1/19/2010)
Your suggestion please.

My suggestion would be... please answer my questions 😀

Here they are...

- how many rows are stored on DATE_SAMPLE table?

- how many rows is supposed to retrieve a tipical query on SAMPLE_DATE column?

- Are queries range-filtering on SAMPLE_DATE column the most typical queries on DATE_SAMPLE table?

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 6

amitabhssinha (1/19/2010)
Hi There All,
I have a table with DateTime field with millions of record. Now I want to retrieve records based on search criteria of DateTime field.
I have written a query, would this be helpful, kindly suggest me some. The Query is as follows:
SELECT * FROM DATE_SAMPLE
WHERE SAMPLE_DATE >= '2003-04-09'
AND SAMPLE_DATE <'2003-04-10'
(OR)
this would be better than above,
SELECT * FROM DATE_SAMPLE WHERE SAMPLE_DATE between '2003-04-09'
AND '2003-04-10'
Please suggest.

To be honest, neither. You really shouldn't be using SELECT *.

To your question, what are you looking to have returned? In the first you will get all records entered on 2003-04-09. The second, however, will return all records entered on 2003-04-09 as well as any entered at midnight on 2003-04-10.

Now if you are asking if using >= and < or BETWEEN. Doesn't really make a difference as the Query Optimizer will convert the BETWEEN to >= and <=.

There are more nuances to what you are asking and that would involve indexing. You really should answer PualB's THIRD question.

amitabhssinha SSC-Addicted Points: 481 More actions · Answer 7

@ Paul's Query -

1. In the table, there are millions of records stored and are keep on increasing frequently.

2. As per based on date search criteria the records has to be filtered out.

3. yes, queries are range-filtering on SAMPLE_DATE