Round of Time near Hours

Analyst_SQL 3,531 Reputation points
2023-01-23T05:47:13.5+00:00

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          

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,736 questions
{count} votes

Accepted answer
  1. PercyTang-MSFT 12,426 Reputation points Microsoft Vendor
    2023-01-23T09:07:00.6433333+00:00

    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:

    User's image

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Analyst_SQL 3,531 Reputation points
    2023-01-23T07:37:32.7733333+00:00

    @Olaf Helper

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