Need To Calculate By Rounded Time Or Date Figure In Sql Server

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

Solution 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 monthFROM@dates;

Solution 2:

Assuming, that your DateTime column named mydatetime

For minutes

if only floored

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

if ROUNDED, then it is much simpler

selectDISTINCTCAST(mydatetime as smalldatetime)
from YourTable

For hours in just the similar way

for days

selectDISTINCTCAST(CONVERT(varchar, mydatetime, 112) asdate)
from YourTable

For months

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