Select Records Between Two Times

[BigD]

This seems so simple yet I can't figure it out.

I have a table that has two important columns for this query.

Column A has varchar
Column B is datetime

All I want to do is select any record that is between 5:00 am and
10:00 am regardless of what date it falls under.

In my brain I see

Select *
From <table>
Where B Between Like ‘%5%am' and Like ‘%10%am'

This chokes and I suppose its because Between is literal and Like is
variable.

I cant convert column B to just times because I still need to see the
date in the results of the query.

Thanks in advance for help

[mountain man]

Re: Select Records Between Two Times

Look up the use of the DATEPART function.
in which you can isolate the HH:MM:SS from
the datetime, such as ...

where datepart(hh,Col umn B) between 5 and 10





"BigD" <dbmeriwether@y ahoo.com> wrote in message
news:68c0bb0f.0 401201256.6fc6c 92d@posting.goo gle.com...[color=blue]
> This seems so simple yet I can't figure it out.
>
> I have a table that has two important columns for this query.
>
> Column A has varchar
> Column B is datetime
>
> All I want to do is select any record that is between 5:00 am and
> 10:00 am regardless of what date it falls under.
>
> In my brain I see
>
> Select *
> From <table>
> Where B Between Like '%5%am' and Like '%10%am'
>
> This chokes and I suppose its because Between is literal and Like is
> variable.
>
> I cant convert column B to just times because I still need to see the
> date in the results of the query.
>
> Thanks in advance for help[/color]

[Erland Sommarskog]

Re: Select Records Between Two Times

[posted and mailed, please reply in news]

BigD (dbmeriwether@y ahoo.com) writes:[color=blue]
> I have a table that has two important columns for this query.
>
> Column A has varchar
> Column B is datetime
>
> All I want to do is select any record that is between 5:00 am and
> 10:00 am regardless of what date it falls under.
>
> In my brain I see
>
> Select *
> From <table>
> Where B Between Like ‘%5%am' and Like ‘%10%am'
>
> This chokes and I suppose its because Between is literal and Like is
> variable.[/color]

The best would be:

WHERE convert(char(8) , B, 108) BETWEEN '05:00:00' AND '10:00:00'

Note that if there are indexes on B, SQL Server will not be able to
use them for this query, at least not for an Index Seek. If you want
that, you would need to extract the time porttion to a separate
column.

--
Erland Sommarskog, SQL Server MVP, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp