SQL provides built-in date functions to extract and manipulate parts of a date value. These functions help analyze and organize time-based data efficiently.
- Functions like DAY(), MONTH(), and YEAR() extract specific components from a date.
- Functions such as DAYNAME() and MONTHNAME() return the readable name of the day or month.
Example: First, we will create a demo SQL database and table, on which we will use the date functions.
1. Extracting Components of a Date
SQL provides built-in date functions to extract specific parts of a date value. Functions like DAY(), MONTH(), and YEAR() help retrieve individual components from a date column.
Query:
ALTER TABLE orders
ADD COLUMN day INT,
ADD COLUMN month INT,
ADD COLUMN year INT;
UPDATE orders
SET day = DAY(order_date),
month = MONTH(order_date),
year = YEAR(order_date);
SELECT * FROM orders;
Output:
2. Adding a Column for Day of the Week
To identify the weekday from a date, SQL provides the DAYNAME() function. It returns the name of the day (e.g., Monday, Tuesday) for a given date.
Query:
ALTER TABLE orders
ADD COLUMN day_of_week VARCHAR(15);
UPDATE orders
SET day_of_week = DAYNAME(order_date);
SELECT id, customer_name, order_date, day_of_week FROM orders;
Output:
3. Adding a Column for Month Name
The MONTHNAME() function is used to extract the full name of the month from a date value. This helps in generating more readable and user-friendly reports.
Query:
ALTER TABLE orders
ADD COLUMN month_name VARCHAR(15);
UPDATE orders
SET month_name = MONTHNAME(order_date);
SELECT id, customer_name, order_date, month_name FROM orders;
Output:
