• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 416
  • Last Modified:

how to filter for time range?

Hi experts,
I have a date/time field and want to know how do you express a filter string to filter for a time range, for example I want to see all records of this month but only between 11AM and 1PM?
0
bfuchs
Asked:
bfuchs
  • 7
  • 5
  • 4
  • +3
3 Solutions
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
SELECT *
FROM your_table
WHERE CAST(your_datetime_column as time) BETWEEN '11:00:00' AND '13:00:00'
0
 
PatHartmanCommented:
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.
0
 
bfuchsAuthor Commented:
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 & "'"
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>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.
0
 
bfuchsAuthor Commented:
Hi Jim,

you're right, sorry about that..

here is the filter string

 Day >= '8/12/2014' And timevalue(Day) >= '09:15 AM'
0
 
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
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.
0
 
bfuchsAuthor Commented:
this is for the first suggestion.

 And Day >= '8/12/2014' And CAST(Day as time) >= '09:15 AM'
0
 
PatHartmanCommented:
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.
0
 
bfuchsAuthor Commented:
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:)
0
 
PortletPaulCommented:
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
0
 
PortletPaulCommented:
here are some ideas for this, one using integers, the other using char(5) based on style 8 (ref below)
select
      *
      , datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate])
from YourTable
where ([adate] >= '20140804' and [aDate] < '20140811')
and (
  
   datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate]) >= 1380 -- 11PM
  or
   datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate]) < 60    -- 1AM
  
  )
order by [aDate]
;

select
      *
      , convert(char(5), [aDate] ,8)
from YourTable
where ([adate] >= '20140804' and [aDate] < '20140811')
and (
  
   convert(char(5), [aDate] ,8) >= '23:00'
  or
   convert(char(5), [aDate] ,8)  < '01:00'
  
  )
order by [aDate]
;

Open in new window

see: http://www.experts-exchange.com/Database/MS-SQL-Server/A_12315-SQL-Server-Date-Styles-formats-using-CONVERT.html

sqlfiddle details:
    CREATE TABLE YourTable
    	([aDate] datetime, [aTime] varchar(5), [DayMins] int)
    ;
    	
    INSERT INTO YourTable
    	([aDate], [aTime], [DayMins])
    VALUES
    	('2014-08-01 00:00:00', '00:00', 0),
    	('2014-08-02 01:00:00', '01:00', 60),
    	('2014-08-03 02:00:00', '02:00', 120),
    	('2014-08-04 03:00:00', '03:00', 180),
    	('2014-08-05 04:00:00', '04:00', 240),
    	('2014-08-06 05:00:00', '05:00', 300),
    	('2014-08-07 06:00:00', '06:00', 360),
    	('2014-08-08 07:00:00', '07:00', 420),
    	('2014-08-09 08:00:00', '08:00', 480),
    	('2014-08-10 09:00:00', '09:00', 540),
    	('2014-08-11 10:00:00', '10:00', 600),
    	('2014-08-01 11:00:00', '11:00', 660),
    	('2014-08-02 12:00:00', '12:00', 720),
    	('2014-08-03 13:00:00', '13:00', 780),
    	('2014-08-04 14:00:00', '14:00', 840),
    	('2014-08-05 15:00:00', '15:00', 900),
    	('2014-08-06 16:00:00', '16:00', 960),
    	('2014-08-07 17:00:00', '17:00', 1020),
    	('2014-08-08 18:00:00', '18:00', 1080),
    	('2014-08-09 19:00:00', '19:00', 1140),
    	('2014-08-10 20:00:00', '20:00', 1200),
    	('2014-08-01 21:00:00', '21:00', 1260),
    	('2014-08-02 22:00:00', '22:00', 1320),
    	('2014-08-03 23:00:00', '23:00', 1380),
    	('2014-08-04 23:00:00', '23:00', 1380),
    	('2014-08-07 23:30:00', '23:30', 1410),
    	('2014-08-04 00:00:00', '00:00', 0)
    ;

**Query 1**:

    select
          *
          , datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate])
    from YourTable
    where ([adate] >= '20140804' and [aDate] < '20140811')
    and (
      
       datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate]) >= 1380 -- 11PM
      or
       datediff(minute,dateadd(dd, datediff(dd,0, [adate]), 0),[adate]) < 60    -- 1AM
      
      )
    order by [aDate]
    

**[Results][2]**:
    
    |                         ADATE | ATIME | DAYMINS | COLUMN_3 |
    |-------------------------------|-------|---------|----------|
    | August, 04 2014 00:00:00+0000 | 00:00 |       0 |        0 |
    | August, 04 2014 23:00:00+0000 | 23:00 |    1380 |     1380 |
    | August, 07 2014 23:30:00+0000 | 23:30 |    1410 |     1410 |


**Query 2**:

    select
          *
          , convert(char(5), [aDate] ,8)
    from YourTable
    where ([adate] >= '20140804' and [aDate] < '20140811')
    and (
      
       convert(char(5), [aDate] ,8) >= '23:00'
      or
       convert(char(5), [aDate] ,8)  < '01:00'
      
      )
    order by [aDate]
    

**[Results][3]**:
    
    |                         ADATE | ATIME | DAYMINS | COLUMN_3 |
    |-------------------------------|-------|---------|----------|
    | August, 04 2014 00:00:00+0000 | 00:00 |       0 |    00:00 |
    | August, 04 2014 23:00:00+0000 | 23:00 |    1380 |    23:00 |
    | August, 07 2014 23:30:00+0000 | 23:30 |    1410 |    23:30 |



  [1]: http://sqlfiddle.com/#!3/a2af8/8

  [2]: http://sqlfiddle.com/#!3/a2af8/8/0

  [3]: http://sqlfiddle.com/#!3/a2af8/8/1

Open in new window

{+ edit} by the way, please be careful when selecting any date or time range that only the "lower boundary" is included and the "upper boundary" is excluded.

for the time range 11PM is the "lower boundary" and 1AM the "upper boundary" because we start at 11PM and conclude at 1AM

BUT
If a record holds the time of 01:00:00 +00000 (1AM precisely) then that record belongs to the NEXT time range (otherwise we count it twice)

For more about this please see: "Beware of Between"
0
 
Gustav BrockCIOCommented:
> I don't think time is a data type in SQL 2005 or SQL Express 2005. (datetime, smalldatetime only I believe)

That is your problem. Thus, first use Convert to extract the time as a string, then Cast this to DateTime which you can filter on reliably:

Select
    *,
    Convert(varchar, Day, 108) As TextTime,
    Cast(Convert(varchar, Day, 108) As DateTime) As TrueTime
From
    dbo.YourTable
Where
    [Day] > '2014-08-12'
    And
    Cast(Convert(varchar, Day, 108) As DateTime)
        Between '11:00' And '13:00'

/gustav
0
 
PortletPaulCommented:
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'
  
  )

Open 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
0
 
Gustav BrockCIOCommented:
> 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
0
 
PortletPaulCommented:
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)
0
 
Gustav BrockCIOCommented:
> 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
0
 
Scott PletcherSenior DBACommented:
>> 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.
0
 
Gustav BrockCIOCommented:
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
0
 
bfuchsAuthor Commented:
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?
0
 
bfuchsAuthor Commented:
@ 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?
0
 
PortletPaulCommented:
between cannot be trusted for date or time ranges

I would treat Scott's method as accurate.
0
 
Scott PletcherSenior DBACommented:
Let's take 11:15am to 2:30pm, as that allows us to see how start and end time are specified separately:

--the direct way, somewhat tricky to code and read perhaps
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(MINUTE, datetime_column) >= 15) OR
      (DATEPART(HOUR, datetime_column) IN (12, 13)) OR
      (DATEPART(HOUR, datetime_column) = 14 AND DATEPART(MINUTE, datetime_column) <= 30))


I know, you want something simpler to code.  But converting to text for date and/or time comparisons is bad for performance and risky, as a text comparison can go wrong in odd ways you don't expect.

One thing we can do, however, is use CROSS APPLY to assign an alias name to the time, which simplifies writing (and reading) the WHERE conditions:

FROM ...
CROSS APPLY (
    SELECT DATEADD(MINUTE, DATEPART(HOUR, datetime_column) * 60 +
        DATEPART(MINUTE, datetime_column), 0) AS datetime_column_time
) AS assign_aliases_1
...
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
     (datetime_column_time >= '11:15' AND
      datetime_column_time <= '14:30')
0
 
bfuchsAuthor Commented:
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!!
0

Featured Post

Microsoft Certification Exam 74-409

VeeamĀ® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

  • 7
  • 5
  • 4
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now