SQL Bucket Time Stamp into incrementsof 2 hours

Question

I want to write a sql to bucket the time into an increment of 2 hours. For example 0-2, 2-4, 6-8, ………18-20, 20-22, 22-24

Time             I want it to be
6/8/2015 20:49       20-22
6/5/2015 12:47       12-14
6/9/2015 16:46       16-18

Thanks,

Answer 1

You can use a case expression and some simple arithmetic to group the time values into buckets:

select
    time, 
    case 
       when datepart(hour, time) % 2 = 0 then  -- n % 2 = 0 determines if hour is even
          cast(datepart(hour, time) as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 2 as varchar(2))
       else  -- hour is odd
          cast(datepart(hour, time) - 1 as varchar(2))
          + '-'
          + cast(datepart(hour, time) + 1 as varchar(2))
    end as bucket
from t

Note that I made the assumption that the odd hours should be bucketed into the even numbered buckets, and that there should not be any odd buckets (like 1-3, 3-5 etc).

Sample SQL Fiddle

Sample output:

|                   time | bucket |
|------------------------|--------|
| June, 08 2015 00:49:00 |    0-2 |
| June, 08 2015 23:49:00 |  22-24 |
| June, 08 2015 20:49:00 |  20-22 |
| June, 05 2015 12:47:00 |  12-14 |
| June, 05 2015 13:47:00 |  12-14 |
| June, 09 2015 16:46:00 |  16-18 |