Cast direction

-- The cast direction is towards number.

-- 1
declare @table table (
    val varchar(max)
)

insert into @table (val)
values ('1'), ('2'), ('a')

select *
from @table
where val between 1 and 2

-- 2
declare @table table (
    val int
)

insert into @table (val)
values (1), (2), (3)

select *
from @table
where val between '1' and '2'