Get the Day of the Week
SELECT DATENAME(dw, GETDATE()) See also DATENAME and DATEPART
Back