How do I select the name for a day of the week?

Selecting the name for a day of the week is fairly straight forward using the DATENAME function and a DATEPART argument. For example:

SELECT DATENAME(weekday, GETDATE()) AS 'MyDay';
returns:

MyDay
------
Sunday
We could also use DATENAME to obtain the name of a month:

SELECT DATENAME(month, GETDATE()) AS 'MyMonth';
Which returns:

MyDay
-------
January
For additional information, please visit: http://msdn2.microsoft.com/en-us/library/ms174395.aspx