Microsoft SQL Server 2005 - using the modulo operator

So I have a silly problem, I have not used much SQL Server before, or any SQL for that matter. I basically have a minor mathematical problem that I need solved, and I thought modulo would be good.

I have a number of dates in the database, but I need them be rounded off to the closest [dynamic integer] (could be anything from 0 to 5000000) which will be input as a parameter each time this query is called.

So I thought I'd use modulo to find the remainder, then subtract that remainder from the date. If there is a better way, or an integrated function, please let me know!

What would be the syntax for that? I've tried a lot of things, but I keep getting error messages like integers/floats/decimals can't be used with the modulo operators. I tried casting to all kinds of numeric datatypes.

Any help would be a开发者_如何学编程ppreciated.

Create function [GetNDates]
(
@NumberOfDates int,
@StartDate DateTime,
@EndDate DateTime
)
Returns @DatesTable table
(
    MyDate DateTime
)
As
Begin
Declare @TotalDays int
Declare @Increment int
Declare @Counter int
Declare @DateCounter DateTime
SET @Counter = 0
SELECT @DateCounter = @StartDate
SELECT @TotalDays = DATEDIFF(day, @StartDate, @EndDate)
SET @Increment = @TotalDays / @NumberOfDates
WHILE @Counter < @NumberOfDates
BEGIN
    SET @DateCounter = DATEADD(Day, @Increment, @DateCounter)
    INSERT INTO @DatesTable (MyDate) VALUES (@DateCounter)
    SET @Counter = @Counter + 1
END
Return
End
GO

select * from dbo.GetNDates(40, '1/1/2010', '12/31/2010')