create function getDayOfWeek
    (@date datetime)
returns varchar(10)
as
    begin
        declare @retVal varchar(10)
        select @retVal=
            case datepart(dw,@date)
                when 1 then 'Sun'
                when 2 then 'Mon'
                when 3 then 'Tue'
                when 4 then 'Wed'
                when 5 then 'Thu'
                when 6 then 'Fri'
                when 7 then 'Sat'
            end
        return @retVal
    end

Then you can just call it as any other function in a select (or however you need it):

select dbo.getDayOfWeek(getdate())