How do you print the day name given a day number in SQL Server 2005 using SQL commands?

Question

I want to be able to pass in a day number like 1, 2, 3 or 7 and it will return the day name like Sunday, Monday, Tuesday or Saturday. I know there is the option of using a case statement but I would like to do this using SQL commands, would this be at all possible?

DECLARE @m VARCHAR
SET @m=1
SELECT CASE WHEN @m=1 THEN 'Sunday' END

I have already commented on this question when looking for an answer but user @harper suggested that I should submit a new question with a full description.

EDIT: there is currently answers are given for case statement mostly except one . so again now i am putting my question again here is

" I would like to do this using SQL commands, would this be at all possible?"

Answer 1

Try this :

declare @m varchar 
set @m=1 
SELECT DATENAME(DW,CAST(@m AS INT))

Answer 2

SQL Server Denali has the CHOOSE function that will make this more concise. In the meantime just use CASE inside the UDF.

CREATE FUNCTION dbo.WeekDay(@d int)
RETURNS VARCHAR(9)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
RETURN 
(
SELECT 
    CASE @d
        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
)
END