Back to cookbooks list Articles Cookbook

How to Get Day Names in SQL Server

Problem:

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

Solution 1:

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:

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:

Let’s extract the weekday name in German.

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

The result is ‘samstag’.

Recommended courses:

Recommended articles:

See also: