The Ultimate Guide To MySQL DATE and Date Functions

The Ultimate Guide to MySQL DATE and Date Functions

In MySQL, the DATE data type is used to store calendar dates (YYYY-MM-DD format). MySQL provides a rich set of date functions to manipulate, format, and compare dates efficiently.

1. Understanding MySQL DATE Data Type

Feature	Details
Format	'YYYY-MM-DD'
Storage	3 bytes
Range	'1000-01-01' to '9999-12-31'
Default Value	'0000-00-00' (if NOT NULL is not specified)

✔ No time component (only the date is stored).

2. Creating a Table with a DATE Column

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    hire_date DATE NOT NULL
);

✔ The hire_date column is required when inserting records.

3. Inserting DATE Values

INSERT INTO employees (name, hire_date) 
VALUES ('Alice Johnson', '2024-05-15');

✔ DATE values must be in 'YYYY-MM-DD' format.

Using CURDATE() to insert today’s date:

INSERT INTO employees (name, hire_date) 
VALUES ('Bob Smith', CURDATE());

✔ CURDATE() returns the current date.

4. Retrieving and Formatting DATE Values

Fetching All Records

SELECT * FROM employees;

Formatting DATE Output

SELECT name, DATE_FORMAT(hire_date, '%M %d, %Y') AS formatted_date 
FROM employees;

✔ Converts '2024-05-15' → 'May 15, 2024'.

5. Updating DATE Values

Change an employee’s hire date:

UPDATE employees 
SET hire_date = '2023-06-01' 
WHERE name = 'Alice Johnson';

✔ Updates the date value.

6. Working with DATE Arithmetic

Adding and Subtracting Days, Months, and Years

✔ Add 30 days to a date:

SELECT DATE_ADD('2024-05-15', INTERVAL 30 DAY);

✔ Subtract 2 months from a date:

SELECT DATE_SUB('2024-05-15', INTERVAL 2 MONTH);

✔ Add 1 year:

SELECT DATE_ADD('2024-05-15', INTERVAL 1 YEAR);

Finding Date Differences

Find the number of days between two dates:

SELECT DATEDIFF('2024-12-31', '2024-01-01'); -- Returns 364

Find the number of months between two dates:

SELECT TIMESTAMPDIFF(MONTH, '2023-01-01', '2024-05-15'); -- Returns 16

7. Comparing DATE Values

Find employees hired after January 1, 2023:

SELECT * FROM employees 
WHERE hire_date > '2023-01-01';

Find employees hired this year:

SELECT * FROM employees 
WHERE YEAR(hire_date) = YEAR(CURDATE());

8. Extracting Parts of a DATE

• Get the year:

  SELECT YEAR(hire_date) FROM employees;
  

• Get the month:

  SELECT MONTH(hire_date) FROM employees;
  

• Get the day:

  SELECT DAY(hire_date) FROM employees;
  

9. Converting DATE to Other Formats

Convert DATE to a text format:

SELECT DATE_FORMAT(hire_date, '%W, %M %d, %Y') FROM employees;

✔ Converts '2024-05-15' → 'Wednesday, May 15, 2024'.

Convert DATE to a numeric timestamp:

SELECT UNIX_TIMESTAMP(hire_date) FROM employees;

✔ Converts '2024-05-15' → Unix timestamp (seconds since 1970-01-01).

10. Special Date Functions

Function	Description	Example
CURDATE()	Returns the current date	'2024-01-31'
NOW()	Returns the current date & time	'2024-01-31 12:34:56'
DAYNAME(date)	Returns the day of the week	'Wednesday'
LAST_DAY(date)	Returns the last day of the month	'2024-02-29'
WEEKDAY(date)	Returns the weekday index (0 = Monday)	2 (Wednesday)

11. Summary

• DATE stores values from '1000-01-01' to '9999-12-31'.
• Use CURDATE() to get the current date.
• Use DATE_ADD() and DATE_SUB() for date calculations.
• Use DATEDIFF() and TIMESTAMPDIFF() to find the difference between dates.
• Use DATE_FORMAT() to format dates for better readability.

Would you like additional real-world use cases, such as tracking events, age calculations, or data filtering? 🚀