Hi, My problem/question is this:

[Pejo]

Hi,

My problem/question is this:
I want to select the rows that has a date that's within office-hours i e I both want to say that I want the rows that has a date between two dates but also that it should be between 08 and 17 every day.
I came up with this (see select statement below), and it works, but I want to make sure that there's no other (better) way to do it as it seems there could be...

table1
------
id
prod_date (date datatype)

select id
from table1
where to_char(prod_date,'yyyy-mm-dd') between '2002-01-01' and '2002-01-31'
and to_char(date,'hh24:mi:ss') between '08:00:00' and '17:00:00'

/PeJo

 

[Turkbear]

You can combine the statements to

select id
from table1
where to_char(prod_date,'yyyy-mm-dd hh24:mi:ss') between '2002-01-01 08:00:00' and '2002-01-31 17:00:00'

If you are using 8i or above you can create a function-based index on that date field and the query will be faster..

create index i_tochar_datefield on table1 to_char(prod_date,'yyyy-mm-dd hh24:mi:ss');


hth

 

[karluk]

I think your solution is the way to go. Unfortunately Turkbear's suggestion won't work because it allows times like '2002-01-04 23:45:00'.

You may be able to marginally improve things. For example, I'm not convinced you need to use to_char in your first clause. But in general "if it's not broken, don't fix it."

 

[HenryMonster]

The classic way to go is

select...
from...
where prod_date between
to_date('01/01/2001 08:00:00', 'mm/dd/yyyy hh24:mi:ss')
and
to_date('01/31/2001 08:00:00', 'mm/dd/yyyy hh24:mi:ss')

It does not matter waht kind of datetime format you choose
as long as it's in compliance with the mask.

 

[sem]

If you have an index on prod_date, the better way is.

select id
from table1
where prod_date between to_date('2002-01-01','yyyy-mm-dd')
and to_date('2002-01-31','yyyy-mm-dd')
and prod_date between trunc(prod_date) + 1/3
and trunc(prod_date) + 17/24

 

[Pejo]

Thanks for the answers, although some of you has misunderstod what I was after...
The question wasn't how to select rows between for example "2001-01-01 08:00:00" and "2001-01-31 17:00:00" but how to select rows between "2001-01-01" and "2001-01-31" that's between 8-17 in the day, ie not times in the evening or the night.
It seems as there's no better solution to the problem than the one I came up with so I think I'll stick to it, but thanks for all your effort!

/PeJo

 

[sem]

I'm sure my query suits your needs completely! It works also without index.

----------------
prod_date between to_date('2002-01-01','yyyy-mm-dd')
and to_date('2002-01-31','yyyy-mm-dd')

means that prod_date is within your interval of dates.

----------------
prod_date between trunc(prod_date) + 1/3
and trunc(prod_date) + 17/24

means that the time part of prod_date is between 8 and 17.
----------------
Another way to write the same is

(prod_date-trunc(prod_date)) --Time part in days
between 8/24 and 17/24