In SQL Server, just as you can use T-SQL to get the month name from a date, you can also use the same methods to get the day name. By day name, I mean Monday or Tuesday for example, and not the date number or number of the day of the week (which you can also get if you need it).

Here are three ways to return the day name from a date in SQL Server using T-SQL.

The FORMAT() Function

The FORMAT() function returns a value formatted in the specified format and optional culture. You can use it to return the day name from a date.

Here’s an example:

DECLARE @date datetime2 = '2018-07-01';
SELECT FORMAT(@date, 'dddd') AS Result;

Result:

+----------+
| Result   |
|----------|
| Sunday   |
+----------+

In this case we provided a format of dddd which is for the day name.

You can also get the short day name by providing ddd as the second argument:

DECLARE @date datetime2 = '2018-07-01';
SELECT FORMAT(@date, 'ddd') AS Result;

Result:

+----------+
| Result   |
|----------|
| Sun      |
+----------+

You can also provide an optional argument to specify the culture.

Here are some examples:

DECLARE @date datetime2 = '2018-07-01';
SELECT 
    FORMAT(@date, 'dddd', 'en-US') AS 'en-US',
    FORMAT(@date, 'dddd', 'es-es') AS 'es-es',
    FORMAT(@date, 'dddd', 'de-de') AS 'de-de',
    FORMAT(@date, 'dddd', 'zh-cn') AS 'zh-cn';

Result:

+---------+---------+---------+---------+
| en-US   | es-es   | de-de   | zh-cn   |
|---------+---------+---------+---------|
| Sunday  | domingo | Sonntag | 星期日  |
+---------+---------+---------+---------+

The DATENAME() Function

The DATENAME() function is similar to the DATEPART() function, except that it returns the name of the specified date part (but only where a name is applicable). That means it returns the week day name or the month name if that’s what you require.

Here’s how to return the week day component of the date:

DECLARE @date datetime2 = '2018-07-01';
SELECT DATENAME(weekday, @date) AS Result;

Result:

+----------+
| Result   |
|----------|
| Sunday   |
+----------+

The DAYNAME() ODBC Scalar Function

If for some reason you need to use an ODBC scalar function, there’s one called DAYNAME() that specifically returns the week day name from a date.

Example:

DECLARE @date datetime2 = '2018-07-01';
SELECT {fn DAYNAME(@date)} AS Result;

Result:

+----------+
| Result   |
|----------|
| Sunday   |
+----------+

When using ODBC scalar functions in T-SQL, they are surrounded by curly braces ({}) and the function name is prefixed with fn.