Sherri
|
| Posted: 09/10/2004, 9:58 AM |
|
For some of my reports, I'd like to give users the ability to say: "I want to view records from Sept. 1 to Sept. 30." They should be able to select their beginning date and ending date for the range, and the appropriate records within those dates should be returned.
How do I do that?
Thanks!
Sherri
|
|
|
 |
peterr
Posts: 5971
|
| Posted: 09/10/2004, 10:39 AM |
|
_________________
Peter R.
YesSoftware Forums Moderator
For product support please visit http://support.yessoftware.com |
 |
|
Sherri
|
| Posted: 09/10/2004, 12:29 PM |
|
Thank you! I LOVE the way you recorded this.
I appreciate it.
Sherri
|
|
|
|
CP Hsiao
|
| Posted: 11/29/2004, 10:44 PM |
|
I use the same procedure, but the result range is from s_date to e_date-1. How to workaround this problem?
Date format: yyyy-mm-dd
DB: Oracle 10g
JBoss3.2.5 with Tomcat 5
CP Hsiao
|
|
|
|
CP Hsiao
|
| Posted: 11/29/2004, 11:25 PM |
|
Btw, I can't use the "+1" method you metioned earlier in
http://forums.codecharge.com/posts.php?post_id=49267
CP Hsiao
|
|
|
|
dataobjx
Posts: 181
|
| Posted: 12/02/2004, 4:56 AM |
|
You may also want to consider using SQL similar to that displayed below. This way, you'll also derive those records that "occur" within the date range where the start date and end date span the date range.
In other words, let's say that the user selected January 2005 as the date range.
Scenario 1:
date_start >= '1/1/2005' and date_end <= '1/31/2005'
Record # date_start date_end
1 1/3/2005 1/29/2005
gives you only those records where the start and end date are within that range. If that is your intention, that's fine.
However, consider scenario 2.
Scenario 2
sSQL = "SELECT * FROM Events "
sSQL = sSQL & "WHERE " sSQL = sSQL & "('" & dteToday & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "('" & dteStart & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "('" & dteEnd & "' BETWEEN date_start AND date_end) OR "
sSQL = sSQL & "(date_start BETWEEN '" & dteStart & "' AND '" & dteEnd & "') OR "
sSQL = sSQL & "(date_end BETWEEN '" & dteStart & "' AND '" & dteEnd & "')"
This SQL will not only derive the same records as derived from scenario 1, plus it will also derive records where "Today" is between the date_start and date_end...
Record # date_start date_end
1 1/3/2005 1/29/2005
2 12/9/2004 2/9/2005
In this instance you'll notice that the date range of record 2 falls within the selected date range... it 'spans' that range.
The SQL in each scenario is valid according to the effect desired. Just something to consider as at some stage - each of us hits a snag that requires the SQL similar to that shown in Scenario 2.
We hope this is helpful to you and others.
_________________
www.DataObjx.net
www.mydigitalapps.com |
|
|
|
|
