Get day of week in SQL Server 2005/2008
Even though SQLMenace's answer has been accepted, there is one important SET
option you should be aware of
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 intset @DefaultDateFirst = @@datefirst--; 7 First day of week is "Sunday" by defaultselect [@DefaultDateFirst] = @DefaultDateFirst set datefirst @DefaultDateFirstselect datename(dw,getdate()) -- Saturdayselect datepart(dw,getdate()) -- 7--; Set the first day of week to * TUESDAY * --; (some people start their week on Tuesdays...)set datefirst 2select datename(dw,getdate()) -- Saturday--; Returns 5 because Saturday is the 5th day since Tuesday.--; Tue 1, Wed 2, Th 3, Fri 4, Sat 5select datepart(dw,getdate()) -- 5 <-- It's not 7!set datefirst @DefaultDateFirst
SELECT CASE DATEPART(WEEKDAY,GETDATE()) WHEN 1 THEN 'SUNDAY' WHEN 2 THEN 'MONDAY' WHEN 3 THEN 'TUESDAY' WHEN 4 THEN 'WEDNESDAY' WHEN 5 THEN 'THURSDAY' WHEN 6 THEN 'FRIDAY' WHEN 7 THEN 'SATURDAY' END