How To Modify Date in SQL Using DATEADD
DATEADD() functions is used to add integer values in day, month, year, minutes, seconds etc. This function does not allow float/decimal values in it. DATEADD function takes three parameters.
First parameter is the datepart (day, month, year, minutes, seconds etc.).
Second parameter is the positive or negative integer values which you want to add or subtract to datepart, and
Third parameter is the date to which you want to add integer values.
Syntax:
SELECT DATEADD(DATEPART,NUMBER,DATE);
Adding or subtracting year in datepart in current date
In the below example, we will add one year to date and see the result.
SELECT DATEADD(YEAR,1,GETDATE()) AS AddOneYearToYearPart
Output

In the below example, we will subtract one year to date and see the result. To subtract the year from date will make the second integer negative.
SELECT DATEADD(YEAR,-1,GETDATE()) AS SubtractOneYearToYearPart

Adding or subtracting month in datepart in current date
In the below example, we will add 2 months to date and see the result.
SELECT DATEADD(MONTH,2,GETDATE()) AS AddMonthToMonthPart
Output

In the below example, we will subtract month to date and see the result. To subtract the month from date will make the second integer negative.
SELECT DATEADD(MONTH,-2,GETDATE()) AS SubtractMonthFromDate

Adding or subtracting days from date
In the below example, we will add 2 months to date provided as string and see the result.
SELECT DATEADD(DAY,10,'2020-03-20 10:02:25.400') AS AddDayToDayPart
Output

In the below example, we will subtract days from date and see the result. To subtract the days from date will make the second integer negative.
SELECT DATEADD(MONTH,-10,'2020-03-20 10:02:25.400') AS SubtractDaysFromDate

DATEADD Function on table Column
We can modify year, month, days, etc. in table column having DATETIME data type. To modify year, month, days, etc. from the table column, first we create a table and insert some rows and then we will get the year from that particular column.
USE HR GO CREATE TABLE tblEmployee ( EmpID INT PRIMARY KEY NOT NULL, EmpName VARCHAR(20), EmpGender VARCHAR(10), EmpAge INT, EmpSalary INT, EmpJoiningDate DATETIME ) GO
Let’s insert some records into tblEmployee table.
USE HR GO INSERT INTO tblEmployee VALUES (1,'Simon','Male',25,25000,'2015-01-01 22:04:49.400'), (2,'Dave','Male',29,15000,'2016-03-25 20:04:55.400'), (3,'Sara','Female',35,20000,'2017-05-01 18:04:35.400'), (4,'Julia','Female',36,35000,'2018-01-10 15:03:45.400'), (5,'Sam','Male',32,35000,'2020-03-20 10:02:25.400') GO
Below is the result of records of table tblEmployee.

Now, below is the query to get year from the EmpJoiningDate column of the table tblEmployee.
USE HR GO SELECT EmpJoiningDate, DATEADD(YEAR,5,EmpJoiningDate) AS AddInYear, DATEADD(MONTH,5,EmpJoiningDate) AS AddInMonth, DATEADD(DAY,5,EmpJoiningDate) AS AddInDay, DATEADD(MINUTE,5,EmpJoiningDate) AS AddInMinute, DATEADD(SECOND,5,EmpJoiningDate) AS AddInSecond, DATEADD(MILLISECOND,5,EmpJoiningDate) AS AddInMillisecond FROM tblEmployee GO
Output
EmpJoiningDate | AddInYear | AddInMonth | AddInDay | AddInMinute | AddInSecond | AddInMillisecond |
---|---|---|---|---|---|---|
2015-01-01 22:04:49.400 | 2020-01-01 22:04:49.400 | 2015-06-01 22:04:49.400 | 2015-01-06 22:04:49.400 | 2015-01-01 22:09:49.400 | 2015-01-01 22:04:54.400 | 2015-01-01 22:04:49.407 |
2016-03-25 20:04:55.400 | 2021-03-25 20:04:55.400 | 2016-08-25 20:04:55.400 | 2016-03-30 20:04:55.400 | 2016-03-25 20:09:55.400 | 2016-03-25 20:05:00.400 | 2016-03-25 20:04:55.407 |
2017-05-01 18:04:35.400 | 2022-05-01 18:04:35.400 | 2017-10-01 18:04:35.400 | 2017-05-06 18:04:35.400 | 2017-05-01 18:09:35.400 | 2017-05-01 18:04:40.400 | 2017-05-01 18:04:35.407 |
2018-01-10 15:03:45.400 | 2023-01-10 15:03:45.400 | 2018-06-10 15:03:45.400 | 2018-01-15 15:03:45.400 | 2018-01-10 15:08:45.400 | 2018-01-10 15:03:50.400 | 2018-01-10 15:03:45.407 |
2020-03-20 10:02:25.400 | 2025-03-20 10:02:25.400 | 2020-08-20 10:02:25.400 | 2020-03-25 10:02:25.400 | 2020-03-20 10:07:25.400 | 2020-03-20 10:02:30.400 | 2020-03-20 10:02:25.407 |
List of DATEPARTS
Below is the list of date parts which can be modified using DATEADD() function in SQL.
nanosecond |
microsecond |
milliscond |
second |
minute |
hour |
weekday |
week |
day |
dayofyear |
month |
quater |
year |
Recommended Readings
- Advanced SQL Queries For Practice With Solution
- SQL Queries For Practice With Solution
- SQL Interview Questions and Answers
- STORED PROCEDURE in SQL Server
- How To Join Tables Data in SQL Server
- How to use Transaction in SQL Stored Procedure
- Difference Between IN and NOT IN Operators in SQL
- How To Modify Date in SQL Using DATEADD
- How To Get Year From Date in SQL Server
- How To Get Month From Date in SQL Server
- How To Get Day From Date in SQL Server
- How To Use ROW_NUMBER Function in SQL
- Date and Time Functions in SQL Server
- How To Find Nth Highest Salary in SQL Server
- How to Backup Table Using SELECT INTO Statement
- How To Use HAVING Clause in SQL Server
- Aggregate Functions in SQL Server
- How To Group Data Using Group By in SQL Server
- How To Truncate Table in SQL Server
- How To Delete Data From Table in SQL Server
- How To Update Table Data in SQL Server
- How To Sort Data Using Order By Clause in SQL
- How To Select Distinct Records in SQL Server
- How to Filter Data From Table in SQL Server
- Round Off Values Using Ceiling and Floor in SQL
- How To Find Square Root Of A Number in SQL Server
- How To Select Data From Table in SQL Server
- How To Insert Data in SQL Server Table
- How To Add NOT NULL Constraint in SQL Server
- How To Add Check Constraint on SQL Server Table
- How To Add Default Constraint on SQL Server Table
- Unique Key Constraint in SQL Server
- How to add Foreign Key Constraint in SQL Server
- How To Add Identity To SQL Server Table Column
- How to add Primary Key Constraint in SQL Server
- How To Create Alter and Drop Table in SQL Server
- How To Create Alter and Drop Database in SQL