Need to calculate by rounded time or date figure in sql server

Question

i have 2 data columns in Ms Sql 2008

uniqueidentifier column as ID and a datetime column with a few results like follows.

2011-11-06 18:02:18.030
2011-11-06 18:02:18.373
2011-11-06 18:02:57.560
2011-11-06 18:02:58.593
2011-11-06 18:03:01.717
2011-11-06 18:03:02.373
2011-11-06 18:03:03.407

Aside from the complexity of the ID column. I'm only interested in grouping the data based om intervals per

minutes(1,5,10,15,30)
hours (1,2)
days(1,5,10)
months(1,2)

The results should be floored or otherwise to yield only 1 unique per interval As follows

2011-11-06 18:02:00 (1 Minute)
2011-11-06 18:03:00 (1 Minute)
2011-11-06 18:04:00 (1 Minute)
2011-11-06 18:05:00 (1 Minute)
2011-11-06 18:06:00 (1 Minute)

Or per day

2011-11-06 00:00:00 (1 Day)
2011-11-07 00:00:00 (1 Day)
2011-11-08 00:00:00 (1 Day)
2011-11-09 00:00:00 (1 Day)
2011-11-10 00:00:00 (1 Day)

etc.

Any constructive suggestions would be welcome.

Answer 1

You can use the same technique to round to any date interval. This relies on integer division

SELECT
    DATEADD(minute, DATEDIFF(minute, 0, foo), 0),              -- whole minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 5 * 5, 0),      -- 5 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 10 * 10, 0),    -- 10 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 15 * 15, 0),    -- 15 minute
    DATEADD(minute, DATEDIFF(minute, 0, foo) / 30 * 30, 0),    -- 30 minute

    DATEADD(hour, DATEDIFF(hour, 0, foo), 0),                  -- whole hour
    DATEADD(hour, DATEDIFF(hour, 0, foo) / 2 * 2, 0),          -- 2 hour

    DATEADD(day, DATEDIFF(day, 0, foo), 0),                    -- whole day
    DATEADD(day, DATEDIFF(day, 0, foo) / 5 * 5, 0),            -- 5 day
    DATEADD(day, DATEDIFF(day, 0, foo) / 10 * 10, 0),          -- 10 day

    DATEADD(month, DATEDIFF(month, 0, foo), 0),                -- whole month
    DATEADD(month, DATEDIFF(month, 0, foo) / 2 * 2, 0)         -- 2 month
FROM
    @dates;