MySQL DAYNAME Function

MySQL DAYNAME() Function

The DAYNAME() function in MySQL is used to return the name of the day of the week for a given date. It’s a straightforward function that simplifies working with dates, especially when you need to retrieve or display the textual representation of the weekday.

Syntax

DAYNAME(date)

• date: A valid DATE, DATETIME, or TIMESTAMP value.

How It Works

The function takes a date as input and returns the full name of the weekday (e.g., 'Monday', 'Tuesday', etc.).

Examples

Here are some practical examples of how to use the DAYNAME() function:

1. Basic Usage

SELECT DAYNAME('2025-01-12') AS DayName;
-- Output: 'Sunday'

2. With DATETIME Values

SELECT DAYNAME('2025-01-12 14:30:00') AS DayName;
-- Output: 'Sunday'

3. Using in a Query

SELECT order_id, order_date, DAYNAME(order_date) AS DayName
FROM orders
WHERE DAYNAME(order_date) = 'Friday';

4. Current Day Name

SELECT DAYNAME(CURDATE()) AS Today;
-- Output: (Depends on the current date)

Use Cases

1. Analyzing Trends by Weekday

   SELECT DAYNAME(sale_date) AS Day, COUNT(*) AS TotalSales
   FROM sales
   GROUP BY Day;
   

2. Filtering Records by Weekday

   SELECT * 
   FROM events 
   WHERE DAYNAME(event_date) = 'Saturday';
   

3. Enhancing Reports with Readable Date Formats

   SELECT 
       order_id, 
       CONCAT(DAYNAME(order_date), ', ', order_date) AS ReadableDate
   FROM orders;
   

Notes

1. Locale Independence: The DAYNAME() function always returns day names in English, regardless of the MySQL server's locale settings.
2. Invalid Dates: If an invalid date is passed, MySQL will return NULL.

   SELECT DAYNAME('2025-02-30') AS InvalidDate;
   -- Output: NULL
   

Conclusion

The DAYNAME() function is an intuitive and helpful feature for working with dates in MySQL. By returning the weekday name, it adds clarity and readability to date-related queries and reports. Whether you’re creating summaries, filtering data, or generating user-friendly reports, DAYNAME() is a great tool to include in your SQL arsenal.