In SQL Server, you can use the FORMAT() function to return the short day name from a date. FORMAT() is a T-SQL function that enables you to format dates and numbers to a specified format.

This function returns its result as a string. Specifically, it returns it as either a nvarchar or null as the case may be.

Example

Here’s an example of returning the short day name from a date.

DECLARE @date date = '2020-10-25';
SELECT FORMAT(@date, 'ddd');

Result:

Sun

By using the format string ddd, we’re specifying that the date should be formatted using its short day name.

The long day name uses dddd as illustrated in the following example.

DECLARE @date date = '2020-10-25';
SELECT 
  FORMAT(@date, 'dddd') AS [dddd],
  FORMAT(@date, 'ddd') AS [ddd];

Result:

 +--------+-------+
 | dddd   | ddd   |
 |--------+-------|
 | Sunday | Sun   |
 +--------+-------+ 

Specifying a Locale

The FORMAT() function accepts a third “culture” parameter that enables you to specify the language that the output should use.

Here’s an example of outputting the result in German.

DECLARE @date date = '2020-10-25';
SELECT 
  FORMAT(@date, 'dddd', 'de-DE') AS [dddd],
  FORMAT(@date, 'ddd', 'de-DE') AS [ddd];

Result:

 +---------+-------+
 | dddd    | ddd   |
 |---------+-------|
 | Sonntag | So    |
 +---------+-------+ 

If the third argument is not provided, the language of the current session is used. Here’s how to check the language of the current session and here’s how to change it.