This code is language-independent

Select CHARINDEX(SUBSTRING('SAT',1,3), 'SUNMONTUEWEDTHUFRISAT') / 3 + 1

while DATEPART() is language dependent. Mixing them may be dangerous. Consider using language dependent code instead

select wdn

from (

select

wdn = datepart(dw,dateadd(d,n,'20010101')), -- any date will do

wds = left(datename(weekday,dateadd(d,n,'20010101')),3) --the same date

from (values (1),(2),(3),(4),(5),(6),(7)) as t(n)

) as t

where wds='sat'