Get Day of Week in SQL 2005/2008

Question

Say I have a date 01/01/2009, I want to find out what day it was e.g. Monday, Tuesday, etc...

Is there a built in function for this in Sql2005/2008? Or do I need to use an auxiliary table?

Answer 1

use datename

select datename(dw,getdate()) --Friday

select datepart(dw,getdate()) --6

Answer 2

Even though SQLMenace's answer has been accepted, there is one important SET option you should be aware of

SET DATEFIRST

DATENAME will return correct date name but not the same DATEPART value if the first day of week has been changed as illustrated below.

declare @DefaultDateFirst int
set @DefaultDateFirst = @@datefirst
--; 7 First day of week is "Sunday" by default
select [@DefaultDateFirst] = @DefaultDateFirst 

set datefirst @DefaultDateFirst
select datename(dw,getdate()) -- Saturday
select datepart(dw,getdate()) -- 7

--; Set the first day of week to * TUESDAY * 
--; (some people start their week on Tuesdays...)
set datefirst 2
select datename(dw,getdate()) -- Saturday
--; Returns 5 because Saturday is the 5th day since Tuesday.
--; Tue 1, Wed 2, Th 3, Fri 4, Sat 5
select datepart(dw,getdate()) -- 5 <-- It's not 7!
set datefirst @DefaultDateFirst