how to filter for time range?

SELECT *
FROM your_table
WHERE CAST(your_datetime_column as time) BETWEEN '11:00:00' AND '13:00:00'

If you are using Access SQL syntax rather than SQL server, you won't be able to use CAST(), you'll have to use an Access function.   TimeValue() returns the time part of a date so that should work.

Select ...
From ..
Where TimeValue(YourDate) Between #11:00 AM# And #2:00 PM#;

You can use the 24 hour clock if you prefer.

Hi experts,

I am trying to use this in a ADP project connected to Sql express 2005.

day is the name of the field where I store the date+time.

below are are both versions I have tried according to your suggestions, however none of them worked.

    If Not IsNull(Me.txtFromTime) Then swhere = swhere & " And CAST(Day as time) >= '" & Me.txtFromTime & "'"
    If Not IsNull(Me.txtFromTime) Then swhere = swhere & " And timevalue(Day) >= '" & Me.txtFromTime & "'"

>I am trying to use this in a ADP project connected to Sql express 2005.
Would have saved us time if you stated that up front.   I assumed SQL as that's a zone I monitor, guessing Pat did the same with Access.

>day is the name of the field where I store the date+time.
Not the best choice, as this is a reserved word which will often require the use of square quotes [ ] when referring to it in code.

Do us a favor and do a Debug.Print swhere  after the above, then copy-paste it into this question.

Hi Jim,

you're right, sorry about that..

here is the filter string

 Day >= '8/12/2014' And timevalue(Day) >= '09:15 AM'

Is there an error message that you're getting?

About all I can offer is that either '09:15 AM' needs to be '09:15', or Day should be [Day].

If this is Access, then I'll back out gracefully and let Pat answer it, as my Access skillsets are not current.

this is for the first suggestion.

 And Day >= '8/12/2014' And CAST(Day as time) >= '09:15 AM'

I'm pretty sure you'll have to use CAST if these are pass-through queries.  You have to use T-SQL syntax.  I don't know if Day is a reserved word in the eyes of SQL Server.  If it is, it needs to be escaped.  It is definitely a reserved word as far as Access is concerned so you always have to use [Day].  Otherwise, Access will assume you mean the day function and give you an error because an argument is missing.

Hi experts,

This cant be the issue as the first part (Day >= '8/12/2014') runs fine.

error message says "type time is not a defined system type"

btw, I will increase the points as I see this is not a simple matter:)

I don't think time is a data type in SQL 2005 or SQL Express 2005. (datetime, smalldatetime only I believe)

Are you able to alter the table for this filtering by time range?
does anyone know if sql express 2005 supports computed columns? (I'd expect it to)

I'm thinking you should work with an integer field based on seconds in the day (or minutes depends on wanted level of precision), but you have to get used to using these numbers not "time" expressions like "11pm", "1am"

OR

Upgrade to a version that supports the time data type

as I have already provided Gustav

styles 8 and 108 produce the same output which includes seconds hh:mm:ss

so you need to truncate the output to hh:mm or char(5) (its fixed width no benefit in varchar but varcar can be used too
i.e.

and (
  
   convert(char(5), [aDate] ,8) >= '23:00'
  or
   convert(char(5), [aDate] ,8)  < '01:00'
  
  )

Select allOpen in new window

and lastly, sigh, I do wish the untruth of between for date ranges was universally understood.
put simply: do not use between for any time unit range

> as I have already provided Gustav

- eh Paul ... not exactly. Mine is much shorter.

> do not use between for any time unit range

That depends. You just have to - as always - know what you are doing. Just remember that it is inclusive, which is what the questioneer specifies (between 11AM and 1PM).
Of course, if he/she means "from 11AM until but not including 1PM", Between .. And cannot be used. And if so, there is no need to chop the seconds:

23:00 >= '23:00'  => True
23:00:10 >= '23:00' => True as well

01:00 < '01:00' => False
01:00:10 < '01:00' => False as well

/gustav

you're right about the time frame I miss read the question, 11;00 to 13:00 is way easier, what was I thinking?

but:
we are asked questions by folks who (more often than not) are here because "know what you are doing" <> true

so, when is we propose between we are guiding them into dangerous ground

avoiding the use of between (>= with <) is safe in all circumstances and regardless of stored data precision
between is not, it is prone to error

why take the risk?

--
oh and shorter isn't a measure of quality :)
might be of neatness

--
still, your short/neat proposition requires truncating HH:MM:SS to 5 chars.
(and I would have it avoid the use of between)

> we are asked questions by folks who (more often than not) are here because "know what you are doing" <> true

which returns true ...

> avoiding the use of between (>= with <) is safe in all circumstances

Yes. Another advantage is, that it is much faster to adjust if the inclusion/exclusion of the boundaries should change.

Also, a little known feature - which you probably are aware of - is, that "Between <lowvalue> And <highvalue>" returns the same as "Between <highvalue> And <lowvalue>". It can either bite you or - as it did once for me - make a form more user friendly.

/gustav

>> for example I want to see all records of this month but only between 11AM and 1PM? <<

DECLARE @month_to_see datetime --set to any day within the desired month
SET @month_to_see = GETDATE()

SELECT ...
FROM ...
WHERE
    datetime_column >= DATEADD(MONTH, DATEDIFF(MONTH, 0, @month_to_see), 0) AND
    datetime_column < DATEADD(MONTH, DATEDIFF(MONTH, 0, @month_to_see) + 1, 0) AND
    DATEPART(HOUR, datetime_column) >= 11 AND
    DATEPART(HOUR, datetime_column) < 13 --11:00 to 12:59:59.997, i.e. up to 1pm

To include minutes, you'll have to use DATEPART(MINUTE, ...) as well.

DATEPART(HOUR, CallTime) between 11 and 13

will return records for 13:01 and onwards until 13:59 as well => more records.

For 1 and 2, study the 100 records of difference and determine if they should be included or not.
And still, we don't know wether 13:00:00 should be included or not.

/gustav

Hi experts,
 first of all, thanks very much for all input, from each comment of yours I get to learn something..

 I tested those suggestions in SSMS and here are the results:

 1-where    convert(char(5), [CallTime] ,8) Between '11:00' And '13:00' - took 14 seconds, returned 15900 records
 2-where Cast(Convert(varchar, CallTime, 108) As DateTime)  Between '11:00' And '13:00' - took 4 seconds, returned 16000 records
 3-where DATEPART(HOUR, CallTime) >= 11 and DATEPART(HOUR, CallTime) < 13 - took 4 seconds, returned 15898 records.

 what could explain the difference?

@ Scott,

To include minutes, you'll have to use DATEPART(MINUTE, ...) as well

can you give an example of that as I would definitely need to use both hours and minutes, like between 11:15 and 01:15 PM.

@all experts,
since I need this solution to be integrated with my access adp application (as described above), would you be able to help me with the following:
1- how do I convert a users input on a text box as 01:15 PM to 13:00?
2 currently in debug mode when I print the value of that text box I get "1/1/1900 1:15:00 PM", how do I get rid of that date portion?

between cannot be trusted for date or time ranges

I would treat Scott's method as accurate.

Hi Guys,
thanks very much for your suggestions, they're all working.

the final code i decided to use is the following:

    If Not IsNull(Me.txtFromTime) Then swhere = swhere & " And convert(char(5), [CallTime] ,8) >= '" & Format(Me.txtFromTime, "hh:mm") & "'"
    If Not IsNull(Me.txtToTime) Then swhere = swhere & " And convert(char(5), [CallTime] ,8) <= '" & Format(Me.txtToTime, "hh:mm") & "'"

the reason i picked this is because this and Scott's are both giving the same qty of records as opposed to the cast version, in my recent production testing, however Scott's version would be difficult for me to maintain.

In either case, great work!!