Add Months in SQL
Date and time operations are as common as querying the data in an SQL table. Especially when dealing with date related calculations, you are going to come across instances where you need to add the month values from a given date.
Take for example where you need to calculate the future dates, handle subscription-based renewals, or even date-based reports or scheduled tasks.
In this post, we will cover the methods that we can use to add months to a given date in SQL databases. It is good to keep in mind that not all the methods that we discuss in this post are supported in all database systems.
Sample Data
Before we dive into the various techniques, let us start by setting up a sample table and populating it with sample data for demonstration purposes. This allows us to better demonstrate each technique more clearly and effectively.
For our table, we create a new table called “orders” including a column for “order_date”. This contains the date for each order.
order_id INT PRIMARY KEY,
order_date DATE,
customer_username VARCHAR(255),
product_purchased VARCHAR(255),
price DECIMAL(10, 2),
quantity INT
);
This sample table contains information about orders that are performed in a given store. This includes the “order_date”, “customer_username”, “product_purchased”, “price”, and “quantity”.
We can then proceed to add a sample data as shown in the following:
VALUES
('2023-01-15', 'alice_s', 'Widget A', 10.99, 3),
('2023-02-20', 'smith_h', 'Widget B', 14.99, 2),
('2023-03-25', 'alice_j', 'Widget C', 19.99, 1),
('2023-04-30', 'wilson_b', 'Widget A', 10.99, 5),
('2023-05-05', 'mary_a', 'Widget B', 14.99, 4);
This should add the sample data into the “orders” table.
DATEADD in SQL Server
The DATEADD() function is supported in SQL Server and it allows us to add or subtract a specific duration to a given date.
For example, we can use it to add months to a given date as demonstrated in the following example query:
FROM orders;
The previous query should be three months to the “order_date” column in the specified table.
DATE_SUB/DATE_ADD in MySQL
In MySQL, we can use the DATE_ADD and DATE_SUB functions to add and/or subtract the months from a given date.
FROM orders;
This should perform similar actions and add it to the “orders_date” columns.
Conclusion
In this guide, we explored the various methods and techniques for adding months to a date in SQL.
Source: linuxhint.com