select Rounding Datetime to nearest 30 minutes

Question

create table #temp (ID varchar(50),DateTime_ Time)

insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000')
insert into #temp (ID,DateTime_) values ('1002','2022-01-01 06:07:24.000')
insert into #temp (ID,DateTime_) values ('1003','2022-01-01 06:15:24.000')
insert into #temp (ID,DateTime_) values ('1004','2022-01-01 06:20:24.000')
insert into #temp (ID,DateTime_) values ('1005','2022-01-01 06:39:24.000')
insert into #temp (ID,DateTime_) values ('1006','2022-01-01 06:46:24.000')
insert into #temp (ID,DateTime_) values ('1007','2022-01-01 16:59:24.000')

select * from #temp

DROP Table #TEMP

-- Result format
-- 05:15 equal and less then 5:01 then result 05:00
-- 05:16 equal and greater then 5:45 then result 05:30
-- 05:46 equal and greater then 5:59 then result 06:00

----------------------

--Data Result view

---------------------

-- ID DateTime_
--1001 6:00
--1002 6:00
--1003 6:30
--1004 6:30
--1005 6:30
--1006 7:00
--1007 7:00

Answer 1

Hi @powsul mohamed ,

Try this:

 create table #temp (ID varchar(50)  
 ,DateTime_ dateTime)  
      
 insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000')  
  ,('1002','2022-01-01 06:07:24.000')  
 ,('1003','2022-01-01 06:15:24.000')  
 , ('1004','2022-01-01 06:20:24.000')  
 ,('1005','2022-01-01 06:39:24.000')  
 , ('1006','2022-01-01 06:46:24.000')  
 , ('1007','2022-01-01 16:59:24.000')  
  
select id,DateTime_,dateadd(MINUTE, round(datediff(MINUTE, ' ', DateTime_) / 30.0, 0) * 30, ' ') dtRounded30 from #temp  

Best regards,
Niko

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If DateTime_ is a time, try one of approaches:

select ID,  
    cast(dateadd(second, (datediff(second, '00:00', DateTime_) + 15*60-1) / (30*60) * (30*60), '00:00') as time) as DateTime_  
from #temp  

Answer 3

create table #temp (ID varchar(50)  
,DateTime_ dateTime)  
  
insert into #temp (ID,DateTime_) values ('1001','2022-01-01 06:01:24.000')  
 ,('1002','2022-01-01 06:07:24.000')  
,('1003','2022-01-01 06:15:24.000')  
, ('1004','2022-01-01 06:20:24.000')  
,('1005','2022-01-01 06:39:24.000')  
, ('1006','2022-01-01 06:46:24.000')  
, ('1007','2022-01-01 16:59:24.000')  
  
select  ID  
 ,datetimefromparts (Year(DateTime_),Month(DateTime_),day(DateTime_)     
 ,datepart(hour,DATEADD(hour, DATEDIFF(hour, 0,DateTime_)+(Case when floor(datepart(minute,DateTime_) /15.) =3  then 1 else 0 end), 0) )  
 ,Case when floor(datepart(minute,DateTime_) /15.) in(1,2) then 30 else 0 end  
 ,0  
 ,0  
 )  dtRounded30  
   
from #temp  
  
DROP Table #TEMP  

Answer 4

See:

https://blog.sqlauthority.com/2012/11/02/sql-server-function-to-round-up-time-to-nearest-minutes-interval/

Answer 5

Here is a solution that uses the new date_bucket function, introduced in SQL 2022, and also available in Azure:

   SELECT ID, DateTime_, date_bucket(minute, 30, dateadd(minute, 15, DateTime_))  
   FROM   #temp  

The date_bucket function makes this problem a lot easier. The only catch here is that date_bucket truncates, but I adjusted for that by adding 15 minutes.