How can i use ' when - between ' statement in sql?

This query gives me syntax error in when-between line. how can i solve them?

alter FUNCTION [dbo].[fn_GetActivityLogsArranger]
(
@time AS nvarchar(max)
)

RETURNS  nvarchar(max)
AS
BEGIN
declare @Return varchar(30)

select @Return = case @time
when between '15:00' and '15:30' then '15:00-15:30'
when between '15:30' and '16:00' then '15:30-16:00'
when between '16:00' and '16:30' then '16:00-16:30'
when between '16:00' and '16:30' then '16:00-16:30' 
when between '16:30' and '17:00' then '16:30-17:00' 
when between '17:00' and '17:30' then '17:00-17:30' 
when between '17:30' and '18:00' then '17:30-18:00'
else 'Unknown'
 Return @Return
end

From stackoverflow

• You can't use that format of the case syntax. You will have to do a case which does checks:

  select @Return = case 
  when @time between '15:00' and '15:30' then '15:00-15:30'
  when @time between '15:30' and '16:00' then '15:30-16:00'
  when @time between '16:00' and '16:30' then '16:00-16:30'
  when @time between '16:00' and '16:30' then '16:00-16:30' 
  when @time between '16:30' and '17:00' then '16:30-17:00' 
  when @time between '17:00' and '17:30' then '17:00-17:30' 
  when @time between '17:30' and '18:00' then '17:30-18:00'
  else 'Unknown' END
  
  Return @Return
  

  Also, you were missing an END at the end of your case statement (see END in uppercase above).

• Well for starters you need to pass your @variable in each when statement

  select @Return = case 
  when @time between ('15:00' and '15:30') then '15:00-15:30'
  when @time between ('15:30' and '16:00') then '15:30-16:00'
  when @time between ('16:00' and '16:30') then '16:00-16:30'
  when @time between ('16:00' and '16:30') then '16:00-16:30' 
  when @time between ('16:30' and '17:00') then '16:30-17:00' 
  when @time between ('17:00' and '17:30') then '17:00-17:30' 
  when @time between ('17:30' and '18:00') then '17:30-18:00'
  else 'Unknown'
  

• alter FUNCTION [dbo].[fn_GetActivityLogsArranger]
  (
      @time AS varchar(30)
  )
  RETURNS  
  varchar(30)AS
  BEGIN
  declare @Return varchar(30)
  select @Return = case 
  when @time between '15:00' and '15:30' then '15:00-15:30'
  when @time between '15:30' and '16:00' then '15:30-16:00'
  when @time between '16:00' and '16:30' then '16:00-16:30'
  when @time between '16:00' and '16:30' then '16:00-16:30' 
  when @time between '16:30' and '17:00' then '16:30-17:00' 
  when @time between '17:00' and '17:30' then '17:00-17:30'
  when @time between '17:30' and '18:00' then '17:30-18:00'
  else 'Unknown' 
  end
  Return @Return
  end
  

  Mitch Wheat : would downvoter mind leaving a comment please. Thanks

  Phsika : Thank alot. you are correct. Look please another ques.http://stackoverflow.com/questions/829089/how-to-call-user-defined-function-in-order-to-use-with-select-group-by-order-by

• You need to have the variable in each WHEN clause eg.

  case 
      when @time between '15:00' and '15:30' then '15:00-15:30'
      when @time between '15:30' and '16:00' then '15:30-16:00'
  

• case syntax : CASE WHEN Boolean_expression THEN result_expression [ ...n ] [ ELSE else_result_expression ] END

• You should not use this function, you should have a table or table valued function of time buckets so you can do a pure join against it. See my other post for an example. The join will outperform by far the approach of calling your function on a row set.