Конвертируем DATE или DATETIME в INT (формата YYYYMMDD)

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select cast(convert(char(8),@dt,112) as int)

Пример обрезки даты по минуты и по часы:

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

Пример округления даты до часа и до минут

declare @dt datetime

set @dt = '09-22-2007 15:07:38.850'

select dateadd(mi, datediff(mi, 0, @dt), 0)
select dateadd(hour, datediff(hour, 0, @dt), 0)

Пример выбора диапазона дат:

WITH    q AS
        (
        SELECT  1 AS num
        UNION ALL
        SELECT  num + 1
        FROM    q
        WHERE   num < 10
        )
SELECT  DATEADD(DAY,0,cast(cast(DATEADD(DAY,-num,SYSDATETIME()) as date) as datetime))   
FROM    q