SQL DayName(), DateName()

The SQL DAYNAME() is a function, and returns the name of the weekday of a given input date value.

The SQL DAYNAME() function returns a String indicating the specified day (Sunday, ..., Saturday) of the week.

The SQL DAYNAME() function is supports only date or datetime based table columns or fields.

It can be used in SELECT statement as well in where clause.

We frequently get into situations when we need to get the Day/Weekday name from a Date in SQL Server.

In SQL Server, we may use the DATENAME() method to extract the name of a day or weekday from a date. We must provide the datepart argument of the DATENAME function as weekday or dw, as both will return the same result.

Example 1: The DATENAME function can be used to obtain the name of the weekday.

SELECT GETDATE() 'Today', DATENAME(weekday,GETDATE()) 'Day Name'
SELECT GetDate() 'Today', DATENAME(dw,GETDATE()) 'Day Name'

Example 2: Using DATENAME function:

SELECT DATENAME(WEEKDAY, GETDATE())

Result:

The FORMAT() function returns a value that has been formatted according to the provided format and culture. It can be used to get the name of the day from a date.

Here’s an example:

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

Result:

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

Example 2: 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:

Get Month Name and Day from Date

The DATENAME() function is identical to the DATEPART() function, only it produces a character string rather than an integer as the output. DATENAME() also delivers the full name of the month and weekday, rather just the number value.

Example:

DECLARE @date datetime2 = '2018-06-02 08:24:14.3112042';
SELECT 
    DATENAME(day, @date) AS DAY,
    DATENAME(weekday, @date) AS WEEKDAY,
    DATENAME(month, @date) AS MONTH,

Result:

We frequently get into situations when we need to get the Day/Weekday name from a Date in SQL Server.

In our illustration, we'll use today's date, which is July 27, 2020, and the day is Monday.

Approach 1: Using DATENAME Function

The DATENAME() method is similar to the DATEPART() function, only it returns the name of the provided date part instead of the date part number (but only where a name is applicable). That is, if you ask for the weekday name or the month name, it will be returned. To retrieve the Day/Weekday name from a Date in SQL Server, use the DATENAME() function.

Example 1: The date is passed together with the name of the day.

You can also use the abbreviation dw or w instead of weekday to get the same result.

DECLARE @DateVal DATE = '2020-07-27';
SELECT @DateVal As [Date], 
    DATENAME(WEEKDAY, @DateVal) AS [Day Name],
    DATENAME(DW, @DateVal) AS [Day Name],
    DATENAME(W, @DateVal) AS [Day Name];

Example 2: 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:

Approach 2: FORMAT Function

The FORMAT() function returns a value that has been formatted according to the provided format and culture. It can be used to get the name of the day from a date.

In SQL Server, there are three ways to get the day name from a date.

To extract the Day/Weekday name from a Date in SQL Server, we can utilise the FORMAT function, which was introduced in SQL Server 2012. It is a.NET CLR-dependent function rather than a SQL Server native function. For retrieving Day/Weekday name from Date in SQL Server, I favour the first way over this one. However, as compared to the DATENAME function, the FORMAT function has a lot more possibilities. If you want to learn more about the FORMAT function, I wrote a detailed post about FORMAT STRING FUNCTION IN SQL SERVER 2012 a few years ago.

Example 1:

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

Result:

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

Example 2: Here is how you can use the format functions to get the date name from the date.

DECLARE @DateVal DATE = '2020-07-27';
SELECT @DateVal As [Date], 
FORMAT(@DateVal, 'dddd') AS [Day Name]

The query above will give us the day name from the date.

Example 3: 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:

Example 4: The DAYNAME() ODBC Scalar Function

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

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

Result:

In T-SQL, ODBC scalar functions are surrounded by curly brackets (), and the function name is prefixed with fn.

The to_char() method in PostgreSQL can be used to extract the day name from a date. The timestamp and template pattern you offer as parameters are used to generate a string.

Returns the day field of a TIMESTAMP value as a string matching to that day's name. The return values vary from 'Sunday' to 'Saturday.' As an alternative to executing DAYOFWEEK() and then using a CASE expression to convert the numeric return result to a string in report-generating queries.

Example 1: To get the day name from timestamp, use dayname() function:

select dayname(yourColumnName) from yourTableName;

Let us first create a table :

mysql> create table DemoTable
(
LoginDate timestamp
);

Insert some records in the table using insert command:

mysql> insert into DemoTable values('2019-06-01');

mysql> insert into DemoTable values('2019-06-02');

mysql> insert into DemoTable values('2019-06-03');

mysql> insert into DemoTable values('2019-06-04');

mysql> insert into DemoTable values('2018-06-21');

Display all records from the table using select statement:

mysql> select *from DemoTable;

Output:

Example 2: Following is the query to get day name from timestamp in MySQL:

mysql> select dayname(LoginDate) from DemoTable;

Output:

Example 3: Here’s a quick example.

SELECT to_char(timestamp '2020-12-16 10:41:35', 'Day') AS "Day";

Result:

In this situation, I utilised the 'Day' template pattern, which resulted in the day name being returned from the timestamp value.

Example 4: Lowercase, Uppercase, and Capitalisation

You can use lowercase, uppercase, or capital letters to return the day. The letter case you choose in the template pattern determines which one is returned (second argument).

Here’s an example to demonstrate what I mean.

SELECT to_char(current_timestamp, 'day') AS "day",
  to_char(current_timestamp, 'Day') AS "Day",
  to_char(current_timestamp, 'DAY') AS "DAY";

Result:

All results are blank-padded to 9 characters, regardless of the case used.

Example 5: Short Day Name Format

You can also specify that the result is formatted using the short day name.

SELECT to_char(current_timestamp, 'dy') AS "dy",
  to_char(current_timestamp, 'Dy') AS "Dy",
  to_char(current_timestamp, 'DY') AS "DY";

Result:

The DAYNAME function in MySQL returns a date's weekday name.

Syntax:

DAYNAME( date_value )

Parameters or Arguments:

date_value - The weekday name should be extracted from a date or datetime data.

Note: Provided a date value, the DAYNAME function returns the name of the weekday (Sunday, Monday, Tuesday, and so on).

Example 1: Let's look at some examples of the MySQL DAYNAME function and see how to use it.

mysql> SELECT DAYNAME('2014-01-27');

Result:

Example 2:

mysql> SELECT DAYNAME('2014-01-28');

Result:

Example 3: Get the dayname with time in this example.

mysql> SELECT DAYNAME('2014-01-29 08:35:17');

Result:

Example 4: The weekday name for the current system date would be displayed in this last DAYNAME instance (current system date is returned by the CURDATE function).

mysql> SELECT DAYNAME(CURDATE());