locked
Return Records Taken on the Hour RRS feed

  • Question

  • I have a DateAndTime column that is in one minute intervals (12:00, 12:01, 12:02, etc).  I want to return only those records that fall on the hour (12:00, 1:00, 2:00, etc).  What's the easiest way to do this?
    Thursday, February 23, 2012 7:35 PM

Answers

  • Declare @myTable Table (myDateTime DateTime)
    Insert Into @myTable 
    Select CURRENT_TIMESTAMP Union All 
    Select DATEADD(Minute, DatePart(Minute, CURRENT_TIMESTAMP) * (-1), CURRENT_TIMESTAMP)
    
    Select * From @myTable 
    Where DATEPART(Minute, myDateTime) = '00'
    
    


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Jay Mazz Thursday, February 23, 2012 7:53 PM
    Thursday, February 23, 2012 7:42 PM

All replies

  • Declare @myTable Table (myDateTime DateTime)
    Insert Into @myTable 
    Select CURRENT_TIMESTAMP Union All 
    Select DATEADD(Minute, DatePart(Minute, CURRENT_TIMESTAMP) * (-1), CURRENT_TIMESTAMP)
    
    Select * From @myTable 
    Where DATEPART(Minute, myDateTime) = '00'
    
    


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    • Marked as answer by Jay Mazz Thursday, February 23, 2012 7:53 PM
    Thursday, February 23, 2012 7:42 PM
  • How about this:

    --Return only those values where minutes are zero:
    SELECT MyColumn
    FROM MyTable
    WHERE DATEPART(mi, MyColumn) = 0


    Diane

    • Proposed as answer by Naomi N Thursday, February 23, 2012 7:47 PM
    Thursday, February 23, 2012 7:43 PM
  • Thank you!
    Thursday, February 23, 2012 7:53 PM
  • You can do this:

    declare @Hours table (tm time)
    
    insert into @Hours
    
    values ('00:00'),('01:00'), ('02:00'),('03:00'),('04:00'),
    ('05:00'),('06:00'), ('07:00'),('08:00'),('09:00'),
    ('10:00'),('11:00'), ('12:00'),('13:00'),('14:00'),
    ('15:00'),('16:00'), ('17:00'),('18:00'),('19:00'),
    ('20:00'),('21:00'), ('22:00'),('23:00')
    
    --select * from @Hours 
    
    declare @RealTable table (tm time)
    declare @Loop int
    
    set @Loop = 0
    while @Loop < 1000
      begin
        insert into @RealTable values (DATEADD(minute, @Loop, '20120101'))
        set @Loop = @Loop + 1
     end
     
     --select * from @RealTable 
     
     select RT.* from @RealTable RT inner join @Hours H ON RT.tm = H.tm 
       

    If the time field is indexed, this may result in better performance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    • Edited by Naomi N Thursday, February 23, 2012 7:57 PM
    Thursday, February 23, 2012 7:55 PM
  • You other alternative would be something like:

    Select * 
    From @myTable 
    Where DATEDIFF(Minute, myDateTime, DateAdd(Hour, DATEPART(Hour, myDateTime), Convert(DateTime, CONVERT(Date, myDateTime)))) = 0 

    And as execution plan it would be same as :

    Select * 
    From @myTable 
    Where DATEPART(Minute, myDateTime) = '00'


    Best Wishes, Arbi; Please vote if you find this posting was helpful or Mark it as answered.

    Thursday, February 23, 2012 7:56 PM