Solved

# SQL Server round date to last quarter of an hour

Posted on 2008-10-23
Medium Priority
1,364 Views
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
Question by:MooksUK

LVL 23

Expert Comment

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
``````
0

LVL 23

Expert Comment

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
``````
0

LVL 14

Accepted Solution

Binuth earned 2000 total points
ID: 22784474
try this
``````declare @dt as datetime
set @dt = cast('01/01/2001 10:59:54' as datetime)
``````
0

LVL 23

Expert Comment

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

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

Expert Comment

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

declare @dt as smalldatetime
set @dt = getdate()

0

## Featured Post

Question has a verified solution.

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

Introduction This article will provide a solution for an error that might occur installing a new SQL 2005 64-bit cluster. This article will assume that you are fully prepared to complete the installation and describes the error as it occurred durin…
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…
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
###### Suggested Courses
Course of the Month8 days, 11 hours left to enroll

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

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