question

kasimmohamed-6993 avatar image
0 Votes"
kasimmohamed-6993 asked EchoLiu-msft answered

Time diff column as parameter

Hi,

I have a table with datetime column like below
80759-image.png


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
80629-image.png
how to achieve this in query



Thanks In Advance

sql-server-generalsql-server-transact-sql
image.png (7.5 KiB)
image.png (10.9 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered kasimmohamed-6993 commented

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.

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks a lot Viorel!!

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered

Hi @kasimmohamed-6993,

 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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.