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

I want to be able to pass in a day number like 1, 2, 3 o开发者_JAVA百科r 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?"

Try this :

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

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

CREATE FUNCTION dbo.udf_DayOfWeek(@dtDate DATETIME)
RETURNS VARCHAR(10)
AS
BEGIN
DECLARE @rtDayofWeek VARCHAR(10)
SELECT @rtDayofWeek = CASE DATEPART(weekday,@dtDate)
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
RETURN (@rtDayofWeek)
END
GO

-- Call this function like this:
SELECT dbo.udf_DayOfWeek(GETDATE()) AS DayOfWeek

orignally from : SQL SERVER – UDF – Get the Day of the Week Function

SELECT DATENAME(DW,CAST(a AS INT))

Here we can change the value of a as 0 to 6
Default, 0-Monday to 6- Sunday.
If we use a=7 then it will be calculated as 0, because 7-7=0.