Help needed querying data between two dates!!!!

[minapatel]

Probably a simple solution but can't find it myself can someone help?????
I would like to extract data from a table where the dates
are between two dates and then loop to select more data for another two sets of dates.

At the moment I have done this select statement


select *
from table_name
where
and date_field between '24-OCT-2005' and '26-OCT-2006'

I have also indexed the date field so that the query response will be quicker
and not cause rollback segment errors.


Please can someone help?

[willakawill]

Probably a simple solution but can't find it myself can someone help?????
I would like to extract data from a table where the dates
are between two dates and then loop to select more data for another two sets of dates.

At the moment I have done this select statement


select *
from table_name
where
and date_field between '24-OCT-2005' and '26-OCT-2006'

I have also indexed the date field so that the query response will be quicker
and not cause rollback segment errors.


Please can someone help?

Hi. Looks like you have one 'and' too many in this query otherwise you are good to go.

you can check this out here

[pragatiswain]

If you want to have a single resultset, you can form a Dynamic query and execute.

select * from table_name
where date_field between '24-OCT-2005' and '26-OCT-2006'
and date_field between @StartDate1 and @EndDate1
and date_field between @StartDate2 and @EndDate2
.....
Hope this helps.

[danibecr]

What about something like this

DEFINE Begin_Date = "&BeginDate"
DEFINE End_Date = "&EndDate"

Select *
FROM ####
WHERE #####
BETWEEN TO_DATE('&Begin_Date'||'000000','MM/DD/YYYYHH24MISS')
AND TO_DATE('&End_Date'||'235959','MM/DD/YYYYHH24MISS')

Then you can define the start and end dates.

[pragatiswain]

Hi danibecr,

My Assumption: Actual data in database in that Date field is only dates without timepart as the example was

select *
from table_name
where
and date_field between '24-OCT-2005' and '26-OCT-2006'

Otherwise,
I would have stated
Select * FROM ####
WHERE TRUNC(#####) BETWEEN @Begin_Date AND @End_Date

One TRUNC() function is faster than execution of (||)String concatination function twice and execution of To_date() function twice.