Retrieve rows that are between two date

i_suman · New User Joined: 01 Mar 2005 Posts: 20

I want to retrieve rows that are between two dates.

I used BETWEEN for this query. Like, BETWEEN date1 AND date2. However we can also use <= and > symbols, instead.

Could some one tell which option to choose to improve the performance of the query? Is it BETWEEN or <, > symbols.

Thanks

dick scherrer · Posted: Sat Oct 25, 2008 9:22 pm

Hello,

You should work with your dba to determine which is best for your particular situation.

nabarundas · New User Joined: 21 Jun 2007 Posts: 28 Location: pune

Try the bellow query:

select * from <Tablename> where days(<day_col_name>) between days(date('yyyy-mm-dd')) and days(date('yyyy-mm-dd'))

also check out the following link

www.ibm.com/developerworks/db2/library/techarticle/0211yip/0211yip3.html

Regards,
Nabarun

enrico-sorichetti · Posted: Wed Oct 29, 2008 2:28 pm

The TS does know how to write the query...

the question is about a performance issue

case 1 ) something between limit1 and limit2
case 2 ) something > limit1 and thesamesomehing < limit2

the result set is the same, but the DB2 retrieval path might be different
full tablescan vs. index use

nabarundas · New User Joined: 21 Jun 2007 Posts: 28 Location: pune

I am sorry. I misjudged the question.

Regards,
Nabarun

muthuvel · Posted: Wed Oct 29, 2008 2:44 pm

When i had the same situation of selecting the current day's inserted or updated record which was fetched by using Update Timestamp in table.
My DBA recommended BETWEEN is better than <,> for performance .

So as earler mentioned by Dick,your DBA can give you the 'best' solution than us.

dharmendra_kp · New User Joined: 10 Mar 2005 Posts: 33 Location: Lewiston

And DBA takes the decision based on the table structure and nature of data population. For example if that date column is part of the key and or an index is defined on that or that date column is say record update date then between will work better. If that date column is something like record insert date then most likey > < is going to be more efficient.

Cristopher · New User Joined: 31 Jul 2008 Posts: 53 Location: NY

Hi, this Link might help

ibmmainframes.com/viewtopic.php?t=33315&highlight=

Cris