CODEWITHSUNDEEP
sqlsql-serversql-server-2008
s-a-n
s-a-n

Reputation: 787

Can we get day number by passing day name?

Is it possible to get a day number of the week from day name in sql server without hard coding? Some thing like this

select DatePart(xx, 'Monday') 
result: 1

Upvotes: 1

Views: 1185

Answers (4)

AswinRajaram
AswinRajaram

Reputation: 1622

There is a function called DAYOFWEEK designed to return the day number corresponding to the date passed. Its format is:

 select {fn DAYOFWEEK(getdate())}

Upvotes: 1

Spider
Spider

Reputation: 524

I found below example by googling.

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

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

Upvotes: 0

Squirrel
Squirrel

Reputation: 24763

if you are looking for a solution that is independent of DATEFIRST and language

; WITH 
week_day AS
(
    SELECT  date_part = datepart(weekday, datecol), date_name = datename(weekday, datecol)
    FROM    (
            VALUES 
            (GETDATE()),     (GETDATE() + 1), (GETDATE() + 2), 
            (GETDATE() + 3), (GETDATE() + 4), (GETDATE() + 5), (GETDATE() + 6)
        ) v (datecol)
)
SELECT  *
FROM    week_day
where   date_name   = 'Tuesday'

Upvotes: 0

Max Szczurek
Max Szczurek

Reputation: 4334

You can get the day number for a date object like this:

SELECT DATEPART(WEEKDAY, GETDATE())

There isn't enough info in 'Monday' for SQL to infer a date; you're just converting text to a number at that point. But, if you must, at least write a function you can reuse:

CREATE FUNCTION dbo.DayNameToDayNumber(@DayName VARCHAR(10))
RETURNS SMALLINT
AS
BEGIN
    RETURN CASE @DayName
        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
        ELSE NULL
        END
END
GO

Upvotes: 2

Related Questions