SELECT DATENAME(dw,'09/23/2013') as theDayName
Here is what the above code is Doing:
1. We are selecting the day name from the date 09/23/2013.
2. We are using the DATENAME function to get the day name.
3. We are using the dw parameter to get the day name.
4. We are using the as keyword to give the column an alias.
5. We are using the alias theDayName to give the column a name.