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

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 2

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 3

I disagree with the answer about adding a lookup table, since in this case, the values are limited and will never change. The lookup table join will just add cost.

IMHO; since you have limited values, I would just use a case statement in my views. It's not pretty, but it's probably the fastest way to do it.

Answer 4

This may not serve a practical purpose, but I thought I'd figure it out just for fun. :) The following works.

Remember that when using DATEPART or DATENAME that the value of @@DATEFIRST is important and can change your results. Look up SET DATEFIRST in the online help.

DECLARE
    @date_name AS VARCHAR(20)

SET @date_name = 'Monday'

SELECT
    DATEPART(dw, my_date)
FROM
    (
    SELECT CAST('1900-01-01' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-02' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-03' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-04' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-05' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-06' AS DATETIME) AS my_date UNION
    SELECT CAST('1900-01-07' AS DATETIME) AS my_date
    ) AS My_Dates
WHERE
    DATENAME(dw, my_date) = @date_name

Answer 5

Are all of your users using English? What is DayOfWeek("Mittwoch")

Answer 6

I need to Convert Day Number to Day Name

is the sql have built in function make this