需要由圆形的时间或日期数字在SQL Server

Question

计算我有2分数据列在SQL女士2008

唯一标识符列作为ID和与像如下几个结果的日期时间列。

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 

除了ID列的复杂性。 我只是在每

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

结果应地板或分组基于OM间隔的数据感兴趣，否则，产生每间隔 只有1独特如下

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) 

或每天

​​

等

任何建设性的建议将受到欢迎。

Answer 1

您可以使用相同的技术来舍入任何日期间隔。这依赖于整数除法

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; 

Answer 2

假设，你的时间列名为mydatetime

对于分钟

如果只地板

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

如果圆润，那么它是非常简单

select DISTINCT CAST(mydatetime as smalldatetime) 
from YourTable 

对于在刚刚同样的方式小时

天

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

几个月

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