SQL Server : selecting 'WEEKDAY' part by passing date in 'ddd' format

Question

How I can retrieve 'WEEKDAY' part by passing 'ddd' format in SQL Server?

For example in if I pass 'tue' and the response will be 3, beacuse

1 - Sunday 2 - Monday 3 - Tuesday

... like that.

I get the 'WEEKDAY' of current date by executing following query.

select DATEPART(WEEKDAY, GETDATE())

Answer 1

This will return the day number for the given short day name honouring the current DATEFIRST setting.

--Example day name
DECLARE @Day CHAR(3) = 'Tue'

SELECT 
    DATEPART(WEEKDAY,DATEADD(DAY, Number, GETDATE())) DayNumber
FROM 
    master..spt_values N
WHERE 
    N.type = 'P' AND N.number BETWEEN 1 AND 7
    AND DATENAME(WEEKDAY,DATEADD(DAY, Number, GETDATE())) LIKE @Day+'%'