MooksUK
asked on
SQL Server round date to last quarter of an hour
How can I round a date down to the last quarter of an hour in SQL for SQL Server?
i.e. 01/01/2001 10:09:54 becomes 01/01/2001 10:00:00 and 01/01/2001 10:46:32becomes 01/01/2001 10:45:00
i.e. 01/01/2001 10:09:54 becomes 01/01/2001 10:00:00 and 01/01/2001 10:46:32becomes 01/01/2001 10:45:00
Wrapped up as a function, simplifies use to:
SELECT dbo.fnRoundToPreviousQuart erHour('20 01-01-01 10:09:54')
SELECT dbo.fnRoundToPreviousQuart
CREATE FUNCTION dbo.fnRoundToPreviousQuarterHour(@Date DATETIME)
RETURNS DATETIME
AS
BEGIN
DECLARE @RoundedDate DATETIME
SET @RoundedDate = CONVERT(VARCHAR(10), @Date, 120)
SELECT @RoundedDate =
CASE
WHEN DATEPART(mi, @Date) >=0 AND DATEPART(mi, @Date) <15 THEN DATEADD(HH, DATEPART(HH, @Date), @RoundedDate)
WHEN DATEPART(mi, @Date) >=15 AND DATEPART(mi, @Date) <30 THEN DATEADD(mi, (DATEPART(HH, @Date) * 60) + 15, @RoundedDate)
WHEN DATEPART(mi, @Date) >=30 AND DATEPART(mi, @Date) <45 THEN DATEADD(mi, (DATEPART(HH, @Date) * 60) + 30, @RoundedDate)
WHEN DATEPART(mi, @Date) >=45 THEN DATEADD(mi, (DATEPART(HH, @Date) * 60) + 45, @RoundedDate)
END
RETURN @RoundedDate
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
I like Binuth's - a lot more concise :)
Does need a small tweak as if you have milliseconds in the original time, those make it through to the result, but if you can sort that out then go for that
Does need a small tweak as if you have milliseconds in the original time, those make it through to the result, but if you can sort that out then go for that
ASKER
Thank to both for your efforts. Neat solution Binuth.
just use smalldatetime if you only need precision to the quarter hour
declare @dt as smalldatetime
set @dt = getdate()
select dateadd(ss,-datepart(ss,@d t),dateadd (mi,-(date part(mi,@d t)%15),@dt ))
declare @dt as smalldatetime
set @dt = getdate()
select dateadd(ss,-datepart(ss,@d
Not necessarily the most elegant but it works...
Open in new window