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

Tags: , ,