day name in sql

SELECT DATENAME(dw, 1)NameofThisDay

#Results:
NameofThisDay
Tuesday

SELECT DATENAME(dw, '2012-08-27 11:12:38.803')NameofThisDay

#Results:
NameofThisDay
Monday

Another example with complex case to get days a week with while loop sql server statement

declare @start int,@end int
declare @qry nvarchar(100)
declare @daysAWeek nvarchar(1000)
set @start=1
set @end=8
set @daysAWeek=''
while not (@start=@end)
    begin
        if @start=@end-1
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName')qry)
        else
            set @qry=(select ('SELECT ('+convert(nvarchar,@start)+')dayNumber,
             DateName(dw,'+convert(nvarchar,@start)+')dayName union ')qry)

        set @daysAWeek=@daysAWeek+@qry       
        set @start=@start+1
    end
    exec (@daysAWeek)

#Results:
dayNumber    dayName
1            Tuesday
2            Wednesday
3            Thursday
4            Friday
5            Saturday
6            Sunday
7            Monday