Select Rounding Datetime to the nearest 30 minutes

Posted: November 30, 2022 | Author: | Filed under: Uncategorized | Leave a comment
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
view raw Select Rounding Datetime to the nearest 30 minutes.sql hosted with ❤ by GitHub

https://learn.microsoft.com/en-us/answers/questions/1108615/select-rounding-datetime-to-nearest-30-minutes.html