Round up DateTime in 15 minute increments

Question

_{Thursday, March 20, 2014 3:27 PM}

I want to round up DateTime in fifteen (15) minute increments. I found some articles on functions, but was not able (due to my limited experience) create a new function. What is the procedure to perform this rounding function? Or, is there a better way of performing. I would like to insert the solution into the Computed Column Specification (formula) in the table so it is automatically performed upon loading.

Any assistance would be appreciated,

Bruce

All replies (4)

_{Thursday, March 20, 2014 5:50 PM ✅Answered}

Visakh,

Thank you very much, works great!

Bruce

---

_{Thursday, March 20, 2014 3:48 PM}

In t-sql you can do like below to round it to nearest 15th minute

See the below illustration

declare @t table
(
dt datetime
)

insert @t
select GETDATE() -120 union all
select GETDATE() -13.6 union all
select GETDATE() -12.44 union all
select GETDATE() -15.10 union all
select GETDATE() -30.65

select *,DATEADD(minute,DATEDIFF(minute,0,dt)/15*15,0)
FROM @t

If you want to make it as a computed column use like below

CREATE TABLE tableName
(
datecolumn datetime,
daterounded AS DATEADD(mi,DATEDIFF(mi,0,datecolumn)/15 *15,0)
...
)

if main colum alread exists use
ALTER TABLE t ableName ADD daterounded AS DATEADD(mi,DATEDIFF(mi,0,datecolumn)/15 *15,0)

Please Mark This As Answer if it helps to solve the issue Visakh http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

---

_{Thursday, March 20, 2014 5:41 PM}

You may check it for exact rounding to the least and highest closest to the time given:

declare @t table
(
dt datetime
)

insert @t
select GETDATE() -120 union all
select GETDATE() -13.6 union all
select GETDATE() -12.44 union all
select GETDATE() -15.10 union all
select GETDATE() -30.65

select *,DATEADD(minute,DATEDIFF(minute,0,dt)/15*15,0)
,Dateadd(minute,case when DATEDIFF(minute,0,dt)%15 <=7 
        then DATEDIFF(minute,0,dt)/15*15 else 15*(DATEDIFF(minute,0,dt)/15+1) end,0)
FROM @t

To have as computed columns:

CREATE TABLE tableName
(
datecolumn datetime,
daterounded AS Dateadd(minute,case when DATEDIFF(minute,0,dt)%15 <=7 
        then DATEDIFF(minute,0,dt)/15*15 else 15*(DATEDIFF(minute,0,dt)/15+1) end,0)
...
)

--if main colum alread exists use
ALTER TABLE tableName ADD daterounded AS Dateadd(minute,case when DATEDIFF(minute,0,dt)%15 <=7 
        then DATEDIFF(minute,0,dt)/15*15 else 15*(DATEDIFF(minute,0,dt)/15+1) end,0)

---

_{Thursday, March 20, 2014 5:52 PM}

Hello BKreft,

Please mark visakh's solution if that works for you!!! Give due credit to him!!!