How to Add Dates in MySQL
November 13, 2023
Adding dates in MySQL is a common task for database management, often requiring precision and understanding of MySQL's date and time functions. The
DATE_ADD function, in particular, is essential for manipulating and calculating dates.
DATE_ADD function in MySQL is used to add a specified time interval to a date.
date: The starting date
expr: The value of the time interval to add
unit: The unit of the time interval (e.g., DAY, MONTH, YEAR)
Examples of DATE_ADD Usage
Adding Days to a Date
This query adds 10 days to January 1, 2023.
Adding Months to a Date
This adds 2 months to January 1, 2023.
Adding Years to a Date
This example adds 1 year to January 1, 2023.
Combining DATE_ADD with Other Functions
Current Date Addition
Adds 15 days to the current date.
Subtracts 3 days from January 1, 2023.
Handling Time Components
Adding Hours, Minutes, and Seconds
Adds 2 hours, 15 minutes, and 30 seconds to the specified datetime.
Advanced DATE_ADD Usage
Adding Complex Intervals
Adds 1 year and 2 months to January 1, 2023.
Using DATE_ADD in Table Updates
Sets the delivery date to 7 days after the order date for a specific order.
Common Mistakes and Tips
- Ensure the date format matches MySQL's standard YYYY-MM-DD or YYYY-MM-DD HH:MM:SS.
- Be cautious with leap years and month-end dates while adding months or years.
- Use the
DATE_SUBfunction for more intuitive date subtraction.
Not Equal in MySQL
How to Drop a User in MySQL
Duplicate Column Name in MySQL
Backticks in MySQL: An Overview
How to Set a Timer in MySQL
How to Fix the Illegal Mix of Collations Error in MySQL