Solved

SQL Server round date to last quarter of an hour

Posted on 2008-10-23
6
1,307 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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
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

Backup Solution for AWS

Read about how CloudBerry Backup fully integrates your backups with Amazon S3 and Amazon Glacier to provide military-grade encryption and dramatically cut storage costs on any platform.

Question has a verified solution.

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

Suggested Solutions

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

680 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