Para obtener el nombre del mes dada una fecha en SQL Server se utiliza la función DATENAME con el primer parámetro month. Un ejemplo de esta función es:

DATENAME(month, '2/4/07') => Resultado: Abril

Si nos interesa saber el nombre del día de la semana, el primer parámetro ha de contener weekday, de manera que partiendo del ejemplo anterior quedaría:

DATENAME(weekday, '2/4/07') => Resultado: Lunes