SQL: Getting Day of Week Name

Script for resolving Day Of Week name of a date
If you need to handle multiple databases in different regional settings,
the value of @@DATEFIRST will be different.
The following can help you on resolving the days of week generically.

DECLARE @ldtTargetDate datetime
SET @ldtTargetDate = '2009-05-14'

SELECT
(CASE
DATEPART(dw,@ldtTargetDate ) + (@@DATEFIRST -1 ) +
(CASE WHEN (DATEPART(dw,@ldtTargetDate ) + (@@DATEFIRST -1))> 7 THEN -7 ELSE 0 END)
WHEN 1 THEN 'MON'
WHEN 2 THEN 'TUE'
WHEN 3 THEN 'WED'
WHEN 4 THEN 'THU'
WHEN 5 THEN 'FRI'
WHEN 6 THEN 'SAT'
WHEN 7 THEN 'SUN'
ELSE '' END
)
AS day_of_week
GO