Solved

SQL Server round date to last quarter of an hour

Posted on 2008-10-23
6
1,300 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
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
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…

831 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