How to Get the Day of the Week from a Date in SQL Server

To get the date of the week from a date in SQL Server, you can use DATENAME Built-in Function:

SELECT DATENAME(dw, GETDATE())

datename_day_of_the_week

SELECT DATENAME(dw, '2013-09-20 15:43:46.463')

datename_day_of_the_week_datetime

SELECT DATENAME(dw, '2013-09-19')

datename_day_of_the_week_date

If you want the result in a different language than the login default language, use the SET LANGUAGE statement with the name of the language from the sys.syslanguages System View:

SET LANGUAGE German
SELECT DATENAME(dw, GETDATE())

datename_day_of_the_week_german

SET LANGUAGE Spanish
SELECT DATENAME(dw, GETDATE())

datename_day_of_the_week_spanish