select DATENAME(weekday,GETDATE()) 'Day Name'

SELECT DATENAME(weekday,GETDATE()) AS [DayName]