Working with Dates in SQL

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: