Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Retrieve rows that are between two date

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
i_suman

New User


Joined: 01 Mar 2005
Posts: 20

PostPosted: Sat Oct 25, 2008 7:05 pm    Post subject: Retrieve rows that are between two date
Reply with quote

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
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Sat Oct 25, 2008 9:22 pm    Post subject:
Reply with quote

Hello,

You should work with your dba to determine which is best for your particular situation.
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Wed Oct 29, 2008 2:19 pm    Post subject: Reply to: Retrieve rows that are between two date
Reply with quote

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

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


Regards,
Nabarun
Back to top
View user's profile Send private message
enrico-sorichetti

Global Moderator


Joined: 14 Mar 2007
Posts: 10231
Location: italy

PostPosted: Wed Oct 29, 2008 2:28 pm    Post subject: Reply to: Retrieve rows that are between two date
Reply with quote

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
Back to top
View user's profile Send private message
nabarundas

New User


Joined: 21 Jun 2007
Posts: 28
Location: pune

PostPosted: Wed Oct 29, 2008 2:33 pm    Post subject:
Reply with quote

I am sorry. I misjudged the question.


Regards,
Nabarun
Back to top
View user's profile Send private message
muthuvel

Active User


Joined: 29 Nov 2005
Posts: 217
Location: Chennai

PostPosted: Wed Oct 29, 2008 2:44 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
dharmendra_kp

New User


Joined: 10 Mar 2005
Posts: 33
Location: Lewiston

PostPosted: Wed Oct 29, 2008 6:21 pm    Post subject:
Reply with quote

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.
Back to top
View user's profile Send private message
Cristopher

New User


Joined: 31 Jul 2008
Posts: 53
Location: NY

PostPosted: Wed Oct 29, 2008 7:34 pm    Post subject: Reply to: Retrieve rows that are between two date
Reply with quote

Hi, this Link might help

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

Cris
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Date and time format in CICS Chandru3183 CICS 2 Sat Mar 18, 2017 12:46 pm
No new posts To display values of multiple rows in... vinu78 DB2 2 Thu Mar 09, 2017 1:20 pm
No new posts How to get current date -1 and curren... murali.andaluri DFSORT/ICETOOL 3 Wed Mar 08, 2017 2:09 pm
No new posts Overlay the system date Danielle.Filteau SYNCSORT 4 Fri Mar 03, 2017 1:16 am
No new posts how to send just 10 rows in a CICS sc... Megha Gupta CICS 5 Thu Feb 23, 2017 6:57 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us