Obtener Descripcion de dia sql

 De esta forma podrías obtener el nombre del día de la semana pero lo devolvería en inglés.

SELECT DATENAME(weekday, getdate()) AS nombreDiaSemana;

Por lo tanto, lo más recomendable sería traer el número del día de la semana y mediante el uso de casos ir determinando el nombre del día de la semana.

SELECT (case DATEPART(dw,getdate()) when 2 then 'Lunes' when 3 then 'Martes' when 4 then 'Miércoles' when 5 then 'Jueves' when 6 then 'Viernes' when 7 then 'Sábado' when 1 then 'Domingo' end) AS nombreDiaSemana;