select based on time

[RamanaSQL]

table: orders
columns: orderid,order,order_date

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

Thanks
R

[marist89]

select * from xyz where dt >=sysdate+12/24 and dt<sysdate+14/24;

[RamanaSQL]

thanks

[RamanaSQL]

hi ,

Is there any other way to do this?

[eddieawad]

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:


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

[RamanaSQL]

Let me try this ..


Thanks

R

[pando]

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:


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



which is worse

[DaPi]

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

[eddieawad]

which is worse
Pando, "worse" is a very vague word. My assumptions were:
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.

[pando]

I didnt see the ANY date so I make a pardon