Converting the name of a day to its integer representation

Question

In SQL server you can use the DATENAME function to get the day of week as a string

declare @date datetime
set @date = '12/16/08'
select datename(dw, @date)

which returns "Tuesday"

and you can use the DATEPART function to get the day of week as an integer

declare @date datetime
set @date = '12/16/08'
select datepart(dw, @date)

Which returns 3

But say I have a varchar that contains the string "Tuesday" and I want to convert it to its integer representation of 3. Sure, I could write out the conversion without much hassle, but I'd much rather use a built-in function. Does such a function exist?

Answer 1

unfortunately there isn't a built in function, but you can create your own like this:

CREATE FUNCTION dbo.WeekDay(@DayOfWeek Varchar(9))
RETURNS INT
            AS
    BEGIN
    DECLARE @iDayofWeek INT
    SELECT @iDayofWeek = CASE @DayOfWeek
                    WHEN 'Sunday' THEN 1
                    WHEN 'Monday' THEN 2
                    WHEN 'Tuesday' THEN 3
                    WHEN 'Wednesday' THEN 4
                    WHEN 'Thursday' THEN 5
                    WHEN 'Friday' THEN 6
                    WHEN 'Saturday' THEN 7
        END
    RETURN (@iDayofWeek)
    END
GO

Answer 2

Rather than write a function, you should create a days of the week table with the description and the numeric value. THen you can simply join to the table to get the numeric.

And if you have days stored multiple ways (likely in a characterbased system), you can put all the variants into the table, so TUE, Tues., Tuesday would all map to the same integer.

Answer 3

Here is a dirty alternative that I use if I'm not running it on massive dataset.

    Select CHARINDEX(SUBSTRING('Thursday',1,3), 'MONTUEWEDTHUFRISATSUN') / 3 + 1

Just replace Thursday with a day of your choice or a variable.

How it works: SUBSTRING('Thursday',1,3) basically creates Thu, then charindex finds out the position of thu in the string which is 10. We then divide 10 by length of our day words which is 3 which equals 3, and because I want

Monday to equal 1 Tuesday to equal 2 Wednesday to equal 3 Thursday to equal 4

I add 1 to the end

so the result is 4.

Hope that helps someone, but I agree it probably isn't the best solution.

Note: You can also order a result set by day number by using it like so:

SELECT ID,DayNamesColumn from mytable ORDER BY CHARINDEX(SUBSTRING(DayNamesColumn ,1,3), 'MONTUEWEDTHUFRISATSUN') / 3 + 1