You are currently viewing How To Modify Date in SQL Using DATEADD

How To Modify Date in SQL Using DATEADD

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

AddYearToDatePart

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
Subtract year from year part

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

Add month to month part in current date

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
Subtract month from date

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

Add day to date

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
Subtract days from date

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.

Day function table data

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