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

DATENAME() functionDECLARE @Date DATE = getdate() SELECT @Date As [TDate], DATENAME(WEEKDAY, @Date) AS [Day_Name];

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