Round of Time near Hours

Question

I want ,round of time nearest hour, means if time is 18:45 or below to 19:00 then round it to 19:00 ,

means that if 45 minutes found or greater than, then it will be round of to hours

if time is 17:44 ,means less then to 17:45 then do not do it round ,

Create table #tbl_Emp_register (Enrollnumber int)
insert into  #tbl_Emp_register values (10053)

select 
 DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 120 ) / 2, '18:45' ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNear,
  DATEADD( minute, ( DATEDIFF( minute, 0, DATEADD( second, ( 15 * 120 ) / 2, '17:44' ) ) / 15 ) * 15, 0 ) AS dateTimeRoundNear
from #tbl_Emp_register 
where EnrollNumbeR=10053 

Output

dateTimeRoundNear      dateTimeRoundNear
      

19:00                       17:44          

Answer 1

Hi @Analyst_SQL

If I understand correctly, you can give it a try.

select dateTimes,case when datepart(mi,dateTimes) >= 45 then 
       DATEADD(minute, (DATEDIFF(minute, 0, DATEADD(second, 15 * 60, dateTimes)) / 15) * 15, 0)
       else dateTimes end as dateTimeRoundNear
from test;

I have tested it.

create table test(dateTimes datetime);
insert into test values
('18:00'),('18:10'),('18:15'),('18:20'),('18:30'),
('18:35'),('18:40'),('18:42'),('18:44'),('18:45'),
('18:46'),('18:59'),('19:44'),('19:45'),('19:46');

output:

Best regards,

Percy Tang

---

If the answer is the right solution, please click "Accept Answer". 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

@Olaf Helper

          I want ,when 15 minutes is less then to 1 Hours then round to it 1 hours ,otherwise do not round it .