T-SQL - Date rounding and normalization
- by arun prakash
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
