How to Get Day Names in SQL Server

Problem:

You want to extract the name of the day of the week from a date in SQL Server.

Solution 1: Get Day Name Using DATENAME()

You can extract the day name from a date using the DATENAME() function. The first parameter is the interval (e.g. year, month, day, etc.) and the second is the date itself. To extract the day name, the interval must have one of the following values: weekday, dw, or w. All will return the same result. Take a look at the example:

SELECT DATENAME(WEEKDAY, '2022-01-01');

The result is 'Saturday'.

There is also an alternative method to get a day name using the function FORMAT(). You’ll need two arguments here: the first is a date and the second is a format. To extract a day name, you’ll need either the format 'dddd' (to get the full name, e.g. “Sunday”) or 'ddd' (to get the short name, e.g. “Sun”).

Solution 2: Get Day Name Using FORMAT()

Let’s get a short weekday name using FORMAT():

SELECT 
  FORMAT(CAST('2022-01-01' AS DATE), 'ddd');

The result is 'Sat'.

Discussion:

Using FORMAT() also lets you extract the day name in another language. You can add another locale (e.g. 'de-de' for Germany) as the third argument to get the weekday name that language. This function accepts any culture supported by the .NET Framework as an argument; you can find a list on the Microsoft Documentation website.

Solution 3: Get Day Name in German

Let’s extract the weekday name in German.

SELECT 
  FORMAT(CAST('2022-01-01' AS DATE), 'dddd', 'de-de');

The result is 'Samstag'.