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.

score 28 · Accepted Answer · answered Jan 04 '12 at 07:15

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;

That is just so cool, and a very neat way of accomplishing this. I must say that I wish there were something built in to accomplish it, but hey, your approach does the trick. — David T. Macknet, Apr 17 '15 at 20:05

score 1 · Answer 2 · answered Jan 04 '12 at 05:20

Assuming, that your DateTime column named mydatetime

For minutes

if only floored

SELECT DISTINCT DATEADD(MINUTE, DATEPART(minute, mydatetime), DATEADD(HOUR, DATEPART(HOUR, mydatetime), CONVERT(varchar, mydatetime, 112)))
from YourTable

if ROUNDED, then it is much simpler

select DISTINCT CAST(mydatetime as smalldatetime)
from YourTable

For hours in just the similar way

for days

select DISTINCT CAST(CONVERT(varchar, mydatetime, 112) as date)
from YourTable

For months

select DISTINCT DATEADD(DAY, 1-DAY(mydatetime), CONVERT(varchar, mydatetime, 112))
from YourTable

What about the other intervals)? Also see http://stackoverflow.com/a/1177529/27535 about best ways to do this — gbn, Jan 04 '12 at 07:06

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

2 Answers2

Linked