Get day name from Datetime using SQL Server 2008 R2

select DATENAME(weekday,getdate())

SELECT CONVERT(VARCHAR(10), '2014-11-14 00:00:00', 105) AS DATE,
       Datename(weekday, '2014-11-14 00:00:00')         AS DayNameofDate 

The function you need is DATENAME. http://msdn.microsoft.com/en-us/library/ms174395.aspx