Solved

SQL Server round date to last quarter of an hour

Posted on 2008-10-23
6
1,284 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

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.

Question has a verified solution.

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

Suggested Solutions

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

770 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