MySQL Date_Sub
Overview
In MySQL, DATE_SUB() is a built-in function used to subtract a specified time interval from a given date or datetime value. The DATE_SUB() function can be useful in many situations, such as when you need to calculate a deadline or expiration date based on a starting date and a duration, or when you need to perform date arithmetic in a SQL query.
Build an AI-First Career, Master the Complete Skillset
Choose from our industry-leading programs designed for career success
Modern Software and AI Engineering Program
Master full-stack development with AI integration
+1000 moreModern Data Science and ML with specialisation in AI
Advanced data science techniques with AI specialization
+1000 moreAdvanced AIML with Specialisation in Agentic AI
Deep dive into AIML with focus on Agentic systems
+1000 moreDevOps, Cloud & AI Platform Engineering
Build and manage AI-powered cloud infrastructure
+1000 moreAI Engineering Advanced Certification by IIT-Roorkee
Premier AI engineering certification from IIT-Roorkee
AI Forward Deployed Engineer Program
Full-stack engineering, production AI and client-facing consulting
+1000 moreSyntax of MySQL DATE_SUB() Function
The DATE_SUB function in MySQL has the following syntax:
Parameters of MySQL DATE_SUB() Function
- Date :
The date from which the interval value must be deducted. - Value :
Here, the value to subtract is a number that is a period of time. Numbers can be both positive and negative. - Unit
The interval's unit type, such as day, month, minute, hour, etc.
Return Value of MySQL DATE_SUB() Function
After subtracting the supplied time or date, it returns the new date.
How Scaler Transformed Careers in Different Fields
Scaler learners achieved 2.5x salary growth with average post-Scaler CTC reaching ₹23L.
Google
Amazon
Microsoft
Flipkart
Adobe1200+ moreHow does the MySQL DATE_SUB() Function Work?
The DATE_SUB() function returns the result after deducting a date or time period from the supplied date or DateTime value.
- It returns a DATE value if date is a DATE value and the unit is YEAR, MONTH, or DAY.
- DATETIME is returned if date is a DATE value and the unit is HOURS, MINUTES, or SECONDS.
- It returns a DATETIME value if the input value is DATETIME.
- DATETIME is returned if date is a TIME value and the unit is YEAR, MONTH, or DAY.
- When the date is a TIME value and just the HOURS, MINUTES, and SECONDS components are used in the calculation, a TIME value is returned. (MySQL version 8.0.28 and later)
- Otherwise, a NULL value is returned. The DATE_SUB() function returns NULL if the first input is an invalid date, a corrupted date, or NULL.
Examples
1) Subtraction of 5 Years
SQL Query:
Output:
2) Subtraction of -2 Months
SQL Query:
Output:
Explanation: Here as we are subtracting -2 months from the specified date, MySQL will add 2 months in the specified date because -(-2) is +2.
3) Subtraction of 13 Days
SQL Query:
Output:
4) Subtraction of 3 Hours
SQL Query:
Output:
5) Subtraction of 5 Minutes
SQL Query:
Output:
6) Subtraction of 2 Seconds
SQL Query:
Output:
7) Subtraction of 2 Quarters
SQL Query:
Output:
8) Subtraction of 9 Weeks
SQL Query:
Output:
9) Subtraction of 13 Days and Some Time
SQL Query:
Output:
10) Subtraction of 2 Days and 5 Hours
SQL Query:
Output:
11) Invalid Date Input
SQL Query:
Output:
Explanation: Here as we are subtracting one day from the specified date but the specified date is invalid as in February month of the year 2021 there were only 28 days so the result will return NULL due to an invalid date as input.
Turn Learning into Career Growth
Conclusion
Important information for the MySQL DATE_SUB() function:
- In MySQL, the DATE_SUB() method is used to subtract a defined time period from a date value.
- Several units of time, including YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, MICROSECOND, WEEK, and QUARTER, can be used to specify the time interval.
- Three parameters are required for the DATE_SUB function: the date or datetime value that needs to be subtracted from; an interval value that indicates the amount of time to be removed; and the unit of the interval value.
- A date or datetime value is returned by MySQL's DATE_SUB function. The data type of the parameter provided to the function determines the return type.
See Also
Related MYSQL functions:
- DATE()
- LAST_DAY()
- DATE_FORMAT()
- DATE_ADD()
- DATEDIFF()
