Hi all

I have a table as following:

calldate callingNumber
2009-02-01 00:00:00 201
2009-02-02 09:00:00 205
2009-02-02 09:10:00 201
2009-02-02 09:40:00 203
2009-02-02 10:10:00 201
2009-02-03 09:20:00 201
2009-02-03 09:23:00 201
2009-02-03 11:20:00 202



I'd like to query all calls made between 09:00:00 and 10:00:00 during a month, for the above table the query result would be the following :


2009-02-02 09:00:00 205
2009-02-02 09:10:00 201
2009-02-02 09:40:00 203
2009-02-03 09:20:00 201
2009-02-03 09:23:00 201

How to write the sql or hql?


Thanks

Lucy

To make it clear, the table is defined as:

CREATE TABLE `tb_bill` (
`calldate` datetime ,
`src` varchar(30),
) ENGINE=InnoDB DEFAULT CHARSET=gbk;

the calldate value is shown as "2009-02-14 00:00:00".

Is it a viable option to split your calldate out into two columns? One for date and one for time of day.

Then you could query with a range clause on the date column, and a separate range clause on the time column.

--
Stephen Souness

The problem would disappear if the date and time is separated into two columns. Unfortunately the table is not designed that way and I can't make a change.

Is this a query that is going to be used a lot?

The SQL queries that I have seen online for this sort of thing tend to involve casting and calling functions on your data - something that is likely to lead to poor performance in your application if the query is used extensively.

--
Stephen Souness