TechBeamersTechBeamers
  • Learn ProgrammingLearn Programming
    • Python Programming
      • Python Basic
      • Python OOP
      • Python Pandas
      • Python PIP
      • Python Advanced
      • Python Selenium
    • Python Examples
    • Selenium Tutorials
      • Selenium with Java
      • Selenium with Python
    • Software Testing Tutorials
    • Java Programming
      • Java Basic
      • Java Flow Control
      • Java OOP
    • C Programming
    • Linux Commands
    • MySQL Commands
    • Agile in Software
    • AngularJS Guides
    • Android Tutorials
  • Interview PrepInterview Prep
    • SQL Interview Questions
    • Testing Interview Q&A
    • Python Interview Q&A
    • Selenium Interview Q&A
    • C Sharp Interview Q&A
    • PHP Interview Questions
    • Java Interview Questions
    • Web Development Q&A
  • Self AssessmentSelf Assessment
    • Python Test
    • Java Online Test
    • Selenium Quiz
    • Testing Quiz
    • HTML CSS Quiz
    • Shell Script Test
    • C/C++ Coding Test
Search
  • Python Multiline String
  • Python Multiline Comment
  • Python Iterate String
  • Python Dictionary
  • Python Lists
  • Python List Contains
  • Page Object Model
  • TestNG Annotations
  • Python Function Quiz
  • Python String Quiz
  • Python OOP Test
  • Java Spring Test
  • Java Collection Quiz
  • JavaScript Skill Test
  • Selenium Skill Test
  • Selenium Python Quiz
  • Shell Scripting Test
  • Latest Python Q&A
  • CSharp Coding Q&A
  • SQL Query Question
  • Top Selenium Q&A
  • Top QA Questions
  • Latest Testing Q&A
  • REST API Questions
  • Linux Interview Q&A
  • Shell Script Questions
© 2024 TechBeamers. All Rights Reserved.
Reading: MySQL DATE_SUB Function with Simple Examples
Font ResizerAa
TechBeamersTechBeamers
Font ResizerAa
  • Python
  • SQL
  • C
  • Java
  • Testing
  • Selenium
  • Agile Concepts Simplified
  • Linux
  • MySQL
  • Python Quizzes
  • Java Quiz
  • Testing Quiz
  • Shell Script Quiz
  • WebDev Interview
  • Python Basic
  • Python Examples
  • Python Advanced
  • Python OOP
  • Python Selenium
  • General Tech
Search
  • Programming Tutorials
    • Python Tutorial
    • Python Examples
    • Java Tutorial
    • C Tutorial
    • MySQL Tutorial
    • Selenium Tutorial
    • Testing Tutorial
  • Top Interview Q&A
    • SQL Interview
    • Web Dev Interview
  • Best Coding Quiz
    • Python Quizzes
    • Java Quiz
    • Testing Quiz
    • ShellScript Quiz
Follow US
© 2024 TechBeamers. All Rights Reserved.
MySQL Tutorial

MySQL DATE_SUB Function with Simple Examples

Last updated: Sep 28, 2023 8:41 pm
By Meenakshi Agarwal
Share
6 Min Read
MySQL DATE_SUB Function with Examples
MySQL DATE_SUB Function with Examples
SHARE

This tutorial explains MySQL DATE_SUB function which subtracts a slice of time (in days/hours, e.g., 1 day or 10 days) from the given date. We’ll describe the complete date arithmetic of this method with the help of simple examples.

Contents
SyntaxMySQL DATE_SUB() ExamplesUsing MySQL DATE_SUB with -ve IntervalCalling MySQL DATE_SUB for Invalid DateMySQL DATE_SUB for Auto Adjustment

1. DATE_SUB() Syntax
2. DATE_SUB() with -ve Interval
3. DATE_SUB() for Invalid Date
4. DATE_SUB() for Auto Adjustment

Let’s now go through each of the sections one by one.

MySQL DATE_SUB() Function

As stated initially, DATE_SUB() is a built-in MySQL function that subtracts the specified number of days from a given date. So, let’s now see the details and check out how can we use it.

Syntax

Below is the signature of this method:

DATE_SUB(Given_date, INTERVAL expr unit);

Below are the descriptions of the parameters in the above function.

+-------------+-----------------------------------------+
| # Params    | # Description                           |
+-------------+-----------------------------------------+
| Given_date  | Date value of DATE or DATETIME type.    |
| expr        | String representing time interval value.|
+-------------+-----------------------------------------+

Let’s take an example that demonstrates the DATE_SUB() function to deduct 2 days from the current date.

SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) result;

In the above statement, we’ve called the MySQL CURDATE() function which returns the date of the present day. Anyways, after executing this command, it gives the following output:

1 MySQL Workbench
2019-07-07

The current date was “2019-07-09” at the time of execution. Hence, after subtracting two days, the result came out as above.

After looking at the arguments, let’s check out what does the DATE_SUB() function returns.

  • The return value would also be a DATE when the input value was of the DATE type.
  • It would return a DATETIME if the input had time in hours, minutes, and seconds.
  • A string value is sent back in all other cases.

Below is an example that calls the DATE_SUB() function to subtract in terms of hours.

SELECT DATE_SUB(CURDATE(), INTERVAL 6 HOUR) result;

After executing the above MySQL command, the result comes as:

1 MySQL Workbench
2019-07-08 18:00:00

MySQL DATE_SUB() Examples

Let’s now unveil several examples addressing different situations.

Using MySQL DATE_SUB with -ve Interval

The second argument (expr) of the DATE_SUB() function can also have -ve interval values.

However, if we pass a negative interval value, then it will act like the DATE_ADD() function. You will get more clarity by seeing the below example.

SELECT DATE_SUB(CURDATE(), INTERVAL -2 day) result;

Since the current date was “2019-07-09” at the time of execution, so the above command added two days to the final result. Check the result/outcome below.

1 MySQL Workbench
2019-07-11

Also Read: MySQL ABS Function with Simple Examples

Calling MySQL DATE_SUB for Invalid Date

It is a negative case when you pass an invalid, malformed, or NULL date value to the DATE_SUB(). This function responds to such inputs by returning a NULL.

SELECT DATE_SUB('2019-13-09', INTERVAL 2 day) result;

The date “2019-13-09” is an incorrect value as the month can’t go beyond 12. Hence, after executing the above command, the output will be:

1 MySQL Workbench
NULL

The MySQL system would also notice this operation and throw a warning. We can track the same by issuing the below command:

SHOW WARNINGS;

The result/output:

Warning 1292 Incorrect datetime value: '2019-13-09'

Now, let’s have one more example to observe the behavior of the MySQL DATE_SUB() function for a malformed date.

-- Input date is malformed
SELECT DATE_SUB('2019-07-09-2019', INTERVAL 2 day) result;
-- Input date is NULL
SELECT DATE_SUB(NULL, INTERVAL 2 day) result;

After running the above MySQL statements, you should see the following outcome:

1 MySQL Workbench
NULL
NULL

Also Read: MySQL FIND_IN_SET Function with Simple Examples

MySQL DATE_SUB for Auto Adjustment

The DATE_SUB() function automatically adjusts the output in the following situation:

  • After subtracting the interval (MONTH, YEAR, or YEAR_MONTH), the result comes to a date that surpasses the maximum day of the new month.

In this scenario, the function normalizes the final date to the last day of the new month. You can observe yourself by running through the example below:

-- Passing '2019-07-31' as the edge date value
SELECT DATE_SUB('2019-07-31', INTERVAL 1 MONTH) result;

After execution, we get this:

1 MySQL Workbench
2019-06-30

In this example, we backtracked by 1 month from July 31st, 2019. Therefore, the output comes to be June 30th, 2019. The day was automatically normalized to June, 30th instead of July, 1st.

We hope that after wrapping up this tutorial, you will feel comfortable using the MySQL DATE_SUB() function. However, you may practice more with examples to gain confidence.

Also, to learn SQL from scratch to depth, read our step-by-step MySQL tutorial.

You Might Also Like

MySQL vs MongoDB Comparison

Concatenate Strings in an SQL Query With Examples

The Difference between UPSERT & Insert

SQL Programming Test in 2024

SQL Table Creation: The Missing Manual

Meenakshi Agarwal Avatar
By Meenakshi Agarwal
Follow:
Hi, I'm Meenakshi Agarwal. I have a Bachelor's degree in Computer Science and a Master's degree in Computer Applications. After spending over a decade in large MNCs, I gained extensive experience in programming, coding, software development, testing, and automation. Now, I share my knowledge through tutorials, quizzes, and interview questions on Python, Java, Selenium, SQL, and C# on my blog, TechBeamers.com.
Previous Article MySQL FIND_IN_SET function with examples MySQL FIND_IN_SET Function with Simple Examples
Next Article MySQL DATE_ADD Function with Examples MySQL DATE_ADD Function with Simple Examples

Popular Tutorials

SQL Interview Questions List
50 SQL Practice Questions for Good Results in Interview
SQL Interview Nov 01, 2016
Demo Websites You Need to Practice Selenium
7 Sites to Practice Selenium for Free in 2024
Selenium Tutorial Feb 08, 2016
SQL Exercises with Sample Table and Demo Data
SQL Exercises – Complex Queries
SQL Interview May 10, 2020
Java Coding Questions for Software Testers
15 Java Coding Questions for Testers
Selenium Tutorial Jun 17, 2016
30 Quick Python Programming Questions On List, Tuple & Dictionary
30 Python Programming Questions On List, Tuple, and Dictionary
Python Basic Python Tutorials Oct 07, 2016
//
Our tutorials are written by real people who’ve put in the time to research and test thoroughly. Whether you’re a beginner or a pro, our tutorials will guide you through everything you need to learn a programming language.

Top Coding Tips

  • PYTHON TIPS
  • PANDAS TIPSNew
  • DATA ANALYSIS TIPS
  • SELENIUM TIPS
  • C CODING TIPS
  • GDB DEBUG TIPS
  • SQL TIPS & TRICKS

Top Tutorials

  • PYTHON TUTORIAL FOR BEGINNERS
  • SELENIUM WEBDRIVER TUTORIAL
  • SELENIUM PYTHON TUTORIAL
  • SELENIUM DEMO WEBSITESHot
  • TESTNG TUTORIALS FOR BEGINNERS
  • PYTHON MULTITHREADING TUTORIAL
  • JAVA MULTITHREADING TUTORIAL

Sign Up for Our Newsletter

Subscribe to our newsletter to get our newest articles instantly!

Loading
TechBeamersTechBeamers
Follow US
© 2024 TechBeamers. All Rights Reserved.
  • About
  • Contact
  • Disclaimer
  • Privacy Policy
  • Terms of Use
TechBeamers Newsletter - Subscribe for Latest Updates
Join Us!

Subscribe to our newsletter and never miss the latest tech tutorials, quizzes, and tips.

Loading
Zero spam, Unsubscribe at any time.
x
x