Solved

SQL Server round date to last quarter of an hour

Posted on 2008-10-23
6
1,327 Views
Last Modified: 2012-05-05
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
0
Comment
Question by:MooksUK
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 23

Expert Comment

by:adathelad
ID: 22784437
Hi,

Not necessarily the most elegant but it works...
DECLARE @OriginalDate DATETIME
SET @OriginalDate = GETDATE()
DECLARE @Date DATETIME
SET @Date = CONVERT(VARCHAR(10), @OriginalDate, 120)
 
SELECT 
	CASE 
		WHEN DATEPART(mi, @OriginalDate) >=0 AND DATEPART(mi, @OriginalDate) <15 THEN DATEADD(HH, DATEPART(HH, @OriginalDate), @Date)
		WHEN DATEPART(mi, @OriginalDate) >=15 AND DATEPART(mi, @OriginalDate) <30 THEN DATEADD(mi, (DATEPART(HH, @OriginalDate) * 60) + 15, @Date)
		WHEN DATEPART(mi, @OriginalDate) >=30 AND DATEPART(mi, @OriginalDate) <45 THEN DATEADD(mi, (DATEPART(HH, @OriginalDate) * 60) + 30, @Date)
		WHEN DATEPART(mi, @OriginalDate) >=45 THEN DATEADD(mi, (DATEPART(HH, @OriginalDate) * 60) + 45, @Date)
	END

Open in new window

0
 
LVL 23

Expert Comment

by:adathelad
ID: 22784456
Wrapped up as a function, simplifies use to:

SELECT dbo.fnRoundToPreviousQuarterHour('2001-01-01 10:09:54')
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

Open in new window

0
 
LVL 14

Accepted Solution

by:
Binuth earned 500 total points
ID: 22784474
try this
declare @dt as datetime
set @dt = cast('01/01/2001 10:59:54' as datetime)
select dateadd(ss,-datepart(ss,@dt),dateadd(mi,-(datepart(mi,@dt)%15),@dt))

Open in new window

0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 23

Expert Comment

by:adathelad
ID: 22784499
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
0
 

Author Comment

by:MooksUK
ID: 22784521
Thank to both for your efforts. Neat solution Binuth.
0
 

Expert Comment

by:grberk
ID: 24843863
just use smalldatetime if you only need precision to the quarter hour

declare @dt as smalldatetime
set @dt = getdate()
select dateadd(ss,-datepart(ss,@dt),dateadd(mi,-(datepart(mi,@dt)%15),@dt))

0

Featured Post

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…

691 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question