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
✔ The hire_date
column is required when inserting records.
3. Inserting DATE Values
✔ DATE values must be in 'YYYY-MM-DD'
format.
Using CURDATE() to insert today’s date:
✔ CURDATE()
returns the current date.
4. Retrieving and Formatting DATE Values
Fetching All Records
Formatting DATE Output
✔ Converts '2024-05-15' → 'May 15, 2024'.
5. Updating DATE Values
Change an employee’s hire date:
✔ Updates the date value.
6. Working with DATE Arithmetic
Adding and Subtracting Days, Months, and Years
✔ Add 30 days to a date:
✔ Subtract 2 months from a date:
✔ Add 1 year:
Finding Date Differences
Find the number of days between two dates:
Find the number of months between two dates:
7. Comparing DATE Values
Find employees hired after January 1, 2023:
Find employees hired this year:
8. Extracting Parts of a DATE
- Get the year:
- Get the month:
- Get the day:
9. Converting DATE to Other Formats
Convert DATE to a text format:
✔ Converts '2024-05-15' → 'Wednesday, May 15, 2024'.
Convert DATE to a numeric timestamp:
✔ 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? 🚀