T-SQL - Date rounding and normalization

Question

Hi:

I have a stored procedure that rounds a column with dates in (yyyy:mm:dd hh:mM:ss) to the nearest 10 minute handle (yyyy:mm:dd hh:mM)

20100303 09:46:3000 ------> 20100303 09:50

but i want to chage it to round it off to the nearest 15 minute handle:

20100303 09:46:3000 ------>20100303 09:45

here is my code :

IF OBJECT_ID(N'[dbo].[SPNormalizeAddWhen]') IS NOT NULL
        DROP PROCEDURE [dbo].[SPNormalizeAddWhen]

GO

CREATE PROCEDURE [dbo].[SPNormalizeAddWhen]
As
declare @colname nvarchar(20)
set @colname='Normalized Add_When'

if not exists (select * from syscolumns where id=object_id('Risk') and name=@colname)
    exec('alter table Risk add [' + @colname  + '] datetime')


declare @sql nvarchar(500)
set @sql='update Risk set [' + @colname + ']=cast(DATEPART(yyyy,[add when]) as nvarchar(4)) + ''-'' + cast(DATEPART(mm,[add when]) as nvarchar(2)) + ''-'' + cast(DATEPART(dd,[add when]) as nvarchar(2)) + '' '' + cast(DATEPART(Hh,[add when]) as nvarchar(2)) + '':''  + cast(round(DATEPART(Mi,[add when]),-1) as nvarchar(2)) '
print @sql
exec(@sql)
GO

Answer 1

To round a time to the nearest 15 minutes...

Select GetDate(), 
       DateAdd(Minute, 15 * Round(DateDiff(minute, 0, GetDate())/15.0, 0), 0)