DECLARE @varDate DateTime
-- temporary variable to store a day
DECLARE @daynm varchar(10)
-- Date as 6th of May 2008
SET @varDate = '05/06/2008'
SELECT @daynm = DATENAME(dw, DATEADD(dd, - DATEPART(dd, @varDate) + 1, @varDate))
IF( @daynm IN ('Saturday', 'Sunday'))
SET @daynm = 'Monday'
-- returns Thursday
I have chosen the approach to keep the weekday in a variable so as to return it to a calling program. If you do not have such a requirement, you can do the same using a SELECT CASE statement too.