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

[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.

[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