Time diff column as parameter

Question

Hi,

I have a table with datetime column like below

if time is between 1 to 2 then the next column value should be 2.
if time is between 2 to 4 then the next column value should be 4. etc., like below

how to achieve this in query

Thanks In Advance

Answer 1

Check this query:

select MyColumn, 
    ((datediff(minute, cast(MyColumn as date), MyColumn) - 1) / (2*60) + 1) * 2 as NextColumn
from @MyTable

And maybe clarify the rules.

Answer 2

Hi @kasim mohamed ,

declare @yourtable table(datetimediff char(25))  
insert into @yourtable values('23/03/2021 0:01'),('23/03/2021 2:00')  
                            ,('23/03/2021 3:00'),('23/03/2021 13:59')  
                               
select datetimediff,  
case when cast(substring(datetimediff,charindex(':',datetimediff)-2,2) as int)%2<>0 then   
cast(substring(datetimediff,charindex(':',datetimediff)-2,2) as int)+1 else  
cast(substring(datetimediff,charindex(':',datetimediff)-2,2) as int)+2 end newcolumn  
from @yourtable  

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our [documentation][1] to enable e-mail notifications if you want to receive the related email notification for this thread.