Results 1 to 6 of 6

Thread: Time Filter

  1. #1
    Join Date
    Feb 2006
    Posts
    3

    Unanswered: Time Filter

    Hi,
    I need to filter my table to return data between 18:30 and 7:00

    I am not sure how to do the half hour and span over midnight.

    I think this might work for 18:00 and 7:00.

    SELECT *
    FROM MyTable
    WHERE datePart(hour, Timestamp) Between 18 AND 24 Or datePart(hour, Timestamp) Between 00 AND 7

    I would appreciate any help!

    Thanks

  2. #2
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    Does your TimeStamp contains date & time ?
    Post some sample data and expected result
    Or Try this :
    declare
    @start_time datetime,
    @end_time datetime

    -- Assuming that your timestamp column contain date & time
    select @start_time = '2006-02-01 18:00',
    @end_time = '2006-02-02 07:00'

    select *
    from #temp
    where [TimeStamp] >= @start_time
    and [TimeStamp] <= @end_time
    -----------------
    KH


  3. #3
    Join Date
    Feb 2006
    Posts
    3
    Thanks khtan

    My TimeStamp does contains date & time.
    Its format is YYYY-MM-DD HH:MMS

    The Timestamp spans years and I am trying to filter the data to just return what occured between 18:30 and 7:00 (outside working hours) for all this time.

    The query is being built in vb and the results are returned to vb for processing.

  4. #4
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    select *
    from #temp
    where convert(varchar(8), [TimeStamp], 108) between '18:30:00' and '23:59:59'
    or convert(varchar(8), [TimeStamp], 108) between '00:00:00' and '07:00:00'

    Note : This will not be very efficient
    -----------------
    KH


  5. #5
    Join Date
    Jan 2006
    Location
    Singapore
    Posts
    47
    if you need to do this frequently, it will be more efficient if you keep a seperate column for time only while keeping your current timestamp column.
    -----------------
    KH


  6. #6
    Join Date
    Feb 2006
    Posts
    3
    Thanks khtan.

    I will try your suggestions

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •