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.
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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
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.
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.