query for date range

I use oledb (ACCESS database) in my application. i want to build a query to retrieve the number of Bookings from my Booking table where the appointment_date_time.timeOfADay is in range of 9am-12pm or 14pm- 7pm, that is (>= 9 and <12) or (>= 14 and < 17). Please help to build the query,

I found some query sample like:

select * from tblstudents where classID='1' and studentstartdate between ('2004-12-03') and ('2004-12-12')

or

WHERE DateField BETWEEN @.StartDate AND @.EndDate

But I dont want to search year and month and day, i just want to search the actual hour of a day. i am stuck with the syntax, please help

Did you try the HOUR function. I will return you the value of the actual hours, this can be filtered like any integer column.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
Wang
What happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang
|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:
> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> > Hi,
> >
> > My query 1 needs to narrow down a time range. I index the column
> > (pos_trandate_time, plus the other on the where clause), but the
> performance
> > still very slow, around 1 minutes. Wherease the query on date only is much
> > faster. What should I do?
> >
> > --query 1, cost 99%
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> > pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> > as tx_msg_type, pos_trans_code as tx_tran_type
> > from pos_txn_log (index=pk_pos_txn_log)
> > where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> > and pos_trandate_time <= '2004-07-05 23:59:59'
> > order by pos_trandate_time
> >
> > -- query 2, cost %1
> > select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> > pos_trandate_time, 8) as tx_time,
> > pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
> pos_seq_no
> > as tx_seq_no, pos_msg_type as tx_msg_type,
> > pos_trans_code as tx_tran_type
> > from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
> and
> > CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> > order by pos_trandate_time
> >
> >
> > Thanks
> > Wang
>
>

query datetime range, how to optimize

Hi,
My query 1 needs to narrow down a time range. I index the column
(pos_trandate_time, plus the other on the where clause), but the performance
still very slow, around 1 minutes. Wherease the query on date only is much
faster. What should I do?
--query 1, cost 99%
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
as tx_msg_type, pos_trans_code as tx_tran_type
from pos_txn_log (index=pk_pos_txn_log)
where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
and pos_trandate_time <= '2004-07-05 23:59:59'
order by pos_trandate_time
-- query 2, cost %1
select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
pos_trandate_time, 8) as tx_time,
pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt, pos_seq_no
as tx_seq_no, pos_msg_type as tx_msg_type,
pos_trans_code as tx_tran_type
from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000' and
CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
order by pos_trandate_time
Thanks
WangWhat happens when you take OFF the index hint
"from pos_txn_log (index=pk_pos_txn_log) "
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Andrew" <Andrew@.discussions.microsoft.com> wrote in message
news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> Hi,
> My query 1 needs to narrow down a time range. I index the column
> (pos_trandate_time, plus the other on the where clause), but the
performance
> still very slow, around 1 minutes. Wherease the query on date only is much
> faster. What should I do?
> --query 1, cost 99%
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time, pos_purchase_amt as tx_amt,
> pos_cashback_amt as tx_cashback_amt, pos_seq_no as tx_seq_no, pos_msg_type
> as tx_msg_type, pos_trans_code as tx_tran_type
> from pos_txn_log (index=pk_pos_txn_log)
> where pos_tid ='80007001' and pos_trandate_time >= '2004-07-05 00:00:00'
> and pos_trandate_time <= '2004-07-05 23:59:59'
> order by pos_trandate_time
> -- query 2, cost %1
> select CONVERT(char(8), pos_trandate_time, 3) as tx_date, CONVERT(char(8),
> pos_trandate_time, 8) as tx_time,
> pos_purchase_amt as tx_amt, pos_cashback_amt as tx_cashback_amt,
pos_seq_no
> as tx_seq_no, pos_msg_type as tx_msg_type,
> pos_trans_code as tx_tran_type
> from pos_txn_log where pos_tid ='80007001' and pos_rid ='11180007000'
and
> CONVERT(char(8), pos_settlementdate, 3) = '05/07/04'
> order by pos_trandate_time
>
> Thanks
> Wang|||Almost no difference. I tested.
I actully use CONVERT(dt_field) function in the where clause, since dt_field
is an indexed field, this may cuase query not using index. So the performace
should be bad, but actually testing shows it is much faster, Strange!!
Thanks
Andrew
"Wayne Snyder" wrote:

> What happens when you take OFF the index hint
> "from pos_txn_log (index=pk_pos_txn_log) "
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Andrew" <Andrew@.discussions.microsoft.com> wrote in message
> news:9A2A9227-8111-4788-B4DA-C96848AEF958@.microsoft.com...
> performance
> pos_seq_no
> and
>
>