select based on time

thanks

hi ,

Is there any other way to do this?

Quote:

---

Originally Posted by RamanaSQL

table: orders
columns: orderid,order,order_date

how do i select all the orders between 12pm and 2pm

Thanks
R

---

Assuming you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type, one way is:

Code:

---

select
    orderid,
    order,
    order_date
from
    orders
where 
    to_number(to_char(order_date, 'HH24MI'))
        between 1200 and 1400

---

Let me try this ..


Thanks

R

Quote:

---

Originally Posted by eddieawad

Assuming you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type, one way is:

Code:

---

select
    orderid,
    order,
    order_date
from
    orders
where 
    to_number(to_char(order_date, 'HH24MI'))
        between 1200 and 1400

---

---

which is worse

Jeff's solution only works for the current day.

Pando, I can't see any way NOT to use a function on the date column if we are talking about any date. So chose between:

a) order_date - trunc(order_date) between 12/24 and 14/24

b) to_char(order_date, 'HH24MI') between '1200' and '1400'

So in the general case, if you need high performance, I'd go for (b) and create a FBI (assuming these are exceptions - i.e. only a small fraction of the table is selected.)

**** OOOPS
Yes, (a) and (b) are both candidates for an FBI. So ignore previous paragraph. :o

Quote:

---

Originally Posted by pando

which is worse

---

Pando, "worse" is a very vague word. My assumptions were:

Quote:

---

Originally Posted by eddieawad

you want orders between 12 PM and 2 PM regardless of the date and that the order_date column is of DATE data type

---

Based on the assumptions above, how would you make the query "better"? other than what DaPi suggested: removing the to_number, and/or creating a function based index.

I didnt see the ANY date so I make a pardon