Round up/down minutes to next quater
-
Hi, I need to round the minutes to the nearest quarter of hour. For example if it is 00:07 (hour:minutes) then it would be 00:15, if 00:05 then it would be 00:00. Any ideas how to go about doing this? Many thanks for your time.
Make a function:
CREATE FUNCTION [dbo].[RoundMinutes]
(@dDateTime datetime)
RETURNS DateTime AS
BEGIN
declare @minutes int
set @minutes = datepart(n,@dDateTime)set @minutes = case when @minutes < 7 then -@minutes
when @minutes < 23 then -@minutes+15
when @minutes < 37 then -@minutes+30
when @minutes < 52 then -@minutes+45
else 60-@minutes
end
return(dateadd(n,@minutes,@dDateTime))
ENDCall it like this:
select dbo.roundminutes(datefield) as RoundedDateField from mytesttable
--EricDV Sig--------- Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them. - Laurence J. Peters