ERROR in SQL order by with case when expression

I prepare a SQL query and I use a case-when expression for ordering purposes. If I used req_amount or apply_date as sort_column it works fine. But I use emp_name for sorting, I get this error

> Conversion failed when converting date and/or time from character string.

Please help me to resolve this.

DECLARE @srch_text varchar(50) = 'Cha'
DECLARE @page_no int = 1
DECLARE @page_size int = 10
DECLARE @sort_column  varchar(50) = 'emp_name'
DECLARE @order varchar(50) = 'ASC'
if (@order  = 'asc' OR @order = 'ASC')
BEGIN
select i.TRN_ID,e.EMP_FULLNAME,i.REQUESTING_AMOUNT,i.BET_APPLY_DATE 
from INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
where e.EMP_FULLNAME LIKE '%'+@srch_text+'%'
ORDER BY 
CASE @sort_column
WHEN 'req_amount' THEN i.REQUESTING_AMOUNT
WHEN 'emp_name' THEN  e.EMP_FULLNAME
WHEN 'apply_date' THEN i.BET_APPLY_DATE
END	  asc 
OFFSET @page_size * ((@page_no - 1)) ROWS FETCH NEXT @page_size ROWS ONLY
END
ELSE
BEGIN
select i.TRN_ID,e.EMP_FULLNAME,i.REQUESTING_AMOUNT,i.BET_APPLY_DATE 
from INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
where e.EMP_FULLNAME LIKE '%'+@srch_text+'%'
ORDER BY 
CASE @sort_column
WHEN 'req_amount' THEN i.REQUESTING_AMOUNT
WHEN 'emp_name' THEN  e.EMP_FULLNAME
WHEN 'apply_date' THEN i.BET_APPLY_DATE
END	desc 
OFFSET @page_size * ((@page_no - 1)) ROWS FETCH NEXT @page_size ROWS ONLY
END

You can use separate ORDER BY expressions.

SELECT
  i.TRN_ID,
  e.EMP_FULLNAME,
  i.REQUESTING_AMOUNT,
  i.BET_APPLY_DATE 
FROM INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
WHERE e.EMP_FULLNAME LIKE '%' + @srch_text + '%'
ORDER BY 
  CASE WHEN @order = 'ASC' AND @sort_column = 'req_amount'  THEN i.REQUESTING_AMOUNT END,
  CASE WHEN @order = 'ASC' AND @sort_column = 'emp_name'    THEN e.EMP_FULLNAME END,
  CASE WHEN @order = 'ASC' AND @sort_column = 'apply_date'  THEN i.BET_APPLY_DATE END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'req_amount' THEN i.REQUESTING_AMOUNT END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'emp_name'   THEN e.EMP_FULLNAME END,
  CASE WHEN @order <> 'ASC' AND @sort_column = 'apply_date' THEN i.BET_APPLY_DATE END
OFFSET @page_size * ((@page_no - 1)) ROWS
FETCH NEXT @page_size ROWS ONLY;

Hvaing said that, it's probably better to just use dynamic SQL, as this is basically a Kitchen Sink Query. This means that indexes will get used properly (although the wildcard search is always going to be a problem).

Note the use of sp_executesql to pass parameters in.

DECLARE @sql nvarchar(max) = '
SELECT
  i.TRN_ID,
  e.EMP_FULLNAME,
  i.REQUESTING_AMOUNT,
  i.BET_APPLY_DATE 
FROM INTERMEDIATE i
INNER JOIN EMPLOYEE e ON i.BET_EMP_NUMBER = e.EMP_NUMBER 
WHERE e.EMP_FULLNAME LIKE ''%'' + @srch_text + ''%''
ORDER BY
  ' +

  CASE @sort_column
  WHEN 'req_amount' THEN 'i.REQUESTING_AMOUNT'
  WHEN 'emp_name'   THEN 'e.EMP_FULLNAME'
  WHEN 'apply_date' THEN 'i.BET_APPLY_DATE'
  ELSE '1'
  END +

  IIF(@order = 'ASC', ' ASC', ' DESC ') + '
OFFSET @page_size * ((@page_no - 1)) ROWS
FETCH NEXT @page_size ROWS ONLY;
';

PRINT @sql;  -- your friend

EXEC sp_executesql @sql,
  N'@srch_text varchar(200),
    @page_size bigint,
    @page_no bigint',
  @srch_text = @srch_text
  @page_size = @page_size,
  @page_no = @page_no;

You might want to take a read of https://stackoverflow.com/questions/70519518/is-there-any-better-option-to-apply-pagination-without-applying-offset-in-sql-se/70520457#70520457 which explains why OFFSET FETCH is slow in general, and what to do instead.