If you need to get the day name (i.e. Monday, Tuesday, etc.)
select datename(weekday,getdate()) As Day_name;