How to Add and Subtract Dates from DateTime Using DATEADD() Function in SQL Server

Tutorialsrack 23/05/2020 SQL SQL Server

In this article, you will learn how to add and subtract years, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, nanoseconds from DateTime in the SQL server. DateTime manipulation is the most common scenario is when we fetching data from the database or storing data in a database. Here, in this article, we will use the SQL server built-in DATEADD() function to add or subtract the days from the specific date.

This article answers your all questions as given below:

How to add or subtract 30 days or specified days to a Date in SQL server?

How to add or subtract quarter to a Date in the SQL server?

How to add or subtract 6 Months or specified months to a specified date in SQL Server?

How to add or subtract 5 years or specified years to a specified Date in SQL Server?

How to add or subtract 1 week or specified weeks to a specified date in SQL Server?

How to add or subtract the day of the year to a specific date in the SQL server?

How to add or subtract weekdays to a specific date in the SQL server?

How to add or subtract hours, minutes or seconds, milliseconds, microseconds, nanoseconds to a specified Date in SQL Server?

DATEADD() Function

This function is simply used to add or subtract a specified number of units of DateTime to a specified DateTime such as year, months, days, hours, minutes, seconds, etc. And this function returns a new date value after adding the value to the date_part.

Syntax

DATEADD (datepart , number , date )

Where,

datepart = datepart is the part of date to which DATEADD adds an integer number.
number = number is the integer value to be added or subtracted to the datepart of the Specified Date.
date = date is a literal date value or an expression that can resolve to a value of type DATE, DATETIME, DATETIMEOFFSET, DATETIME2, SMALLATETIME, or TIME.

The list of datepart is as follows:

DatePart	Abbreviations
year	yy, yyyy
quarter	qq, q
month	mm, m
dayofyear	dy, y
day	dd, d
week	wk, ww
weekday	dw, w
hour	hh
minute	mi, n
second	ss, s
millisecond	ms
microsecond	mcs
nanosecond	ns

Note: DATEADD does not accept user-defined variable equivalents for the datepart arguments.

Note:- Each datepart and its abbreviations return the same value.

Examples of DATEADD() Function

How to add or subtract 30 days or specified days to a Date in SQL server?

In this example, you will learn how to add or subtract days to a specified date using the DATEADD function in SQL Server.

Add or Subtract Days

-- Add Days To A Date
SELECT DATEADD(DD, 10, '2020-05-01') AS 'ADD 10 Days To A Date'
-- Output ==> 2020-05-11 00:00:00.000

SELECT DATEADD(DD, 30, '2020-05-01') AS 'ADD 30 Days To A Date'
-- Output ==> 2020-05-31 00:00:00.000

SELECT DATEADD(d, 10, '2020-05-01') AS 'ADD 10 Days To A Date'
-- Output ==> 2020-05-11 00:00:00.000

SELECT DATEADD(DAY, 50, '2020-05-01') AS 'ADD 50 Days To A Date'
-- Output ==> 2020-06-20 00:00:00.000

-----------------------------------------------------------------
-- Subtract Days to A Date

SELECT DATEADD(DD, -10, '2020-05-01') AS 'Subtract 10 Days To A Date'
-- Output ==> 2020-04-21 00:00:00.000

SELECT DATEADD(DD, -30, '2020-05-01') AS 'Subtract 30 Days To A Date'
-- Output ==> 2020-04-01 00:00:00.000

SELECT DATEADD(d, -10, '2020-05-01') AS 'Subtract 10 Days To A Date'
-- Output ==> 2020-04-21 00:00:00.000

SELECT DATEADD(DAY, -50, '2020-05-01') AS 'Subtract 50 Days To A Date'
-- Output ==> 2020-03-12 00:00:00.000

How to add or subtract quarter to a Date in the SQL server?

In this example, you will learn how to add or subtract a quarter to a specified date using DATEADD function in SQL Server.

Add or Subtract Quarter

-- ADD Quarter To A Date
SELECT DATEADD(QQ, 2, '2020-05-01') AS 'ADD Quarter To A Date'
-- Output ==> 2020-11-01 00:00:00.000

SELECT DATEADD(Q, 2, '2020-05-01') AS 'ADD Quarter To A Date'
-- Output ==> 2020-11-01 00:00:00.000

SELECT DATEADD(QUARTER, 2, '2020-05-01') AS 'ADD Quarter To A Date'
-- Output ==> 2020-11-01 00:00:00.000

--------------------------------------------------------------

-- Subtract Quarter To A Date
SELECT DATEADD(QQ, -2, '2020-05-01') AS 'Subtract Quarter To A Date'
-- Output ==> 2019-11-01 00:00:00.000

SELECT DATEADD(Q, -2, '2020-05-01') AS 'Subtract Quarter To A Date'
-- Output ==> 2019-11-01 00:00:00.000

SELECT DATEADD(QUARTER, -2, '2020-05-01') AS 'Subtract Quarter To A Date'
-- Output ==> 2019-11-01 00:00:00.000

How to add or subtract 6 Months or specified months to a specified date in SQL Server?

In this example, you will learn how to add or subtract 6 months or specified months to a specified date in SQL Server.

Add or Subtract Months

-- ADD Month To A Date
SELECT DATEADD(MM, 6, '2020-05-01') AS 'ADD Month To A Date'
-- Output ==> 2020-11-01 00:00:00.000

SELECT DATEADD(M, 6, '2020-05-01') AS 'ADD Month To A Date'
-- Output ==> 2020-11-01 00:00:00.000

SELECT DATEADD(MONTH, 6, '2020-05-01') AS 'ADD Month To A Date'
-- Output ==> 2020-11-01 00:00:00.000

--------------------------------------------------------------

-- Subtract Month To A Date
SELECT DATEADD(MM, -6, '2020-05-01') AS 'Subtract Month To A Date'
-- Output ==> 2019-11-01 00:00:00.000

SELECT DATEADD(M, -6, '2020-05-01') AS 'Subtract Month To A Date'
-- Output ==> 2019-11-01 00:00:00.000

SELECT DATEADD(MONTH, -6, '2020-05-01') AS 'Subtract Month To A Date'
-- Output ==> 2019-11-01 00:00:00.000

How to add or subtract 5 years or specified years to a specified Date in SQL Server?

In this example, you will learn how to add or subtract 5 years or specified years to a specified date in SQL Server.

Add or Subtract Years

-- ADD Year To A Date
SELECT DATEADD(YYYY, 5, '2020-05-01') AS 'ADD Year To A Date'
-- Output ==> 2025-05-01 00:00:00.000

SELECT DATEADD(YY, 5, '2020-05-01') AS 'ADD Year To A Date'
-- Output ==> 2025-05-01 00:00:00.000

SELECT DATEADD(YEAR, 5, '2020-05-01') AS 'ADD Year To A Date'
-- Output ==> 2025-05-01 00:00:00.000

--------------------------------------------------------------

-- Subtract Year To A Date
SELECT DATEADD(YYYY, -5, '2020-05-01') AS 'Subtract Year To A Date'
-- Output ==> 2015-05-01 00:00:00.000

SELECT DATEADD(YY, -5, '2020-05-01') AS 'Subtract Year To A Date'
-- Output ==> 2015-05-01 00:00:00.000

SELECT DATEADD(YEAR, -5, '2020-05-01') AS 'Subtract Year To A Date'
-- Output ==> 2015-05-01 00:00:00.000

How to add or subtract 1 week or specified weeks to a specified date in SQL Server?

In this example, you will learn how to add or subtract 1 week or specified weeks to a specified date in SQL Server.

Add or Subtract Weeks

-- ADD Weeks To A Date
SELECT DATEADD(WK, 1, '2020-05-01') AS 'ADD Weeks To A Date'
-- Output ==> 2020-05-08 00:00:00.000

SELECT DATEADD(WW, 1, '2020-05-01') AS 'ADD Weeks To A Date'
-- Output ==> 2020-05-08 00:00:00.000

SELECT DATEADD(WEEK, 1, '2020-05-01') AS 'ADD Weeks To A Date'
-- Output ==> 2020-05-08 00:00:00.000

--------------------------------------------------------------

-- Subtract Weeks To A Date
SELECT DATEADD(WK, -1, '2020-05-01') AS 'Subtract Weeks To A Date'
-- Output ==> 2020-04-24 00:00:00.000

SELECT DATEADD(WW, -1, '2020-05-01') AS 'Subtract Weeks To A Date'
-- Output ==> 2020-04-24 00:00:00.000

SELECT DATEADD(WEEK, -1, '2020-05-01') AS 'Subtract Weeks To A Date'
-- Output ==> 2020-04-24 00:00:00.000

How to add or subtract the day of the year to a specific date in the SQL server?

In this example, you will learn how to add dayofyear to a specific date or subtract dayofyear from a specific date in the SQL server.

Add or Subtract DayofYear

-- ADD DayofYear To A Date
SELECT DATEADD(DY, 5, '2020-05-01') AS 'ADD DayofYear To A Date'
-- Output ==> 2020-05-06 00:00:00.000

SELECT DATEADD(Y, 5, '2020-05-01') AS 'ADD DayofYear To A Date'
-- Output ==> 2020-05-06 00:00:00.000

SELECT DATEADD(DAYOFYEAR, 5, '2020-05-01') AS 'ADD DayofYear To A Date'
-- Output ==> 2020-05-06 00:00:00.000

--------------------------------------------------------------

-- Subtract DayofYear To A Date
SELECT DATEADD(DY, -5, '2020-05-01') AS 'Subtract DayofYear From A Date'
-- Output ==> 2020-04-26 00:00:00.000

SELECT DATEADD(Y, -5, '2020-05-01') AS 'Subtract DayofYear From A Date'
-- Output ==> 2020-04-26 00:00:00.000

SELECT DATEADD(DAYOFYEAR, -5, '2020-05-01') AS 'Subtract DayofYear From A Date'
-- Output ==> 2020-04-26 00:00:00.000

How to add or subtract weekdays to a specific date in the SQL server?

In this example, you will learn how to add or subtract weekdays to a specific date in the SQL server.

Add or Subtract Weekday

-- ADD WeekDay To A Date
SELECT DATEADD(DW, 5, '2020-05-01') AS 'ADD WeekDay To A Date'
-- Output ==> 2020-05-06 00:00:00.000

SELECT DATEADD(W, 5, '2020-05-01') AS 'ADD WeekDay To A Date'
-- Output ==> 2020-05-06 00:00:00.000

SELECT DATEADD(WEEKDAY, 5, '2020-05-01') AS 'ADD WeekDay To A Date'
-- Output ==> 2020-05-06 00:00:00.000

--------------------------------------------------------------

-- Subtract WeekDay To A Date
SELECT DATEADD(DW, -5, '2020-05-01') AS 'Subtract WeekDay To A Date'
-- Output ==> 2020-04-26 00:00:00.000

SELECT DATEADD(W, -5, '2020-05-01') AS 'Subtract WeekDay To A Date'
-- Output ==> 2020-04-26 00:00:00.000

SELECT DATEADD(WEEKDAY, -5, '2020-05-01') AS 'Subtract WeekDay To A Date'
-- Output ==> 2020-04-26 00:00:00.000

How to add or subtract hours, minutes, seconds, milliseconds, microseconds, nanoseconds to a specified Date in SQL Server?

In this example, you will learn how to add or subtract hours, minutes, seconds, milliseconds, microseconds, nanoseconds to a specified date in the SQL server.

Add or Subtract Unit of Time like Hours, Minutes, Seconds, Milliseconds, etc.

-- Declare a Date With Time
DECLARE @Date DATETIME2 = '2020-05-20 17:47:12.2730000'

-- ADD Time Units To A Date

-- Add Hours to A DateTime
SELECT DATEADD(HH, 1, @Date) AS 'ADD HOURS To A Date'
-- Output ==> 2020-05-20 18:47:12.2730000

-- Add Minutes to A DateTime
SELECT DATEADD(MI, 10, @Date) AS 'ADD Minutes To A Date'
-- Output ==> 2020-05-20 17:57:12.2730000

-- Add Seconds to A DateTime
SELECT DATEADD(SS, 1500, @Date) AS 'ADD Seconds To A Date'
-- Output ==> 2020-05-20 18:12:12.2730000

-- Add MilliSeconds to A DateTime
SELECT DATEADD(MS, 1500, @Date) AS 'ADD MilliSeconds To A Date'
-- Output ==> 2020-05-20 17:47:10.7730000

-- Add MicroSeconds to A DateTime
SELECT DATEADD(MCS, 150000, @Date) AS 'ADD MicroSeconds To A Date'
-- Output ==> 2020-05-20 17:47:12.4230000

-- Add Nano Seconds to A DateTime
SELECT DATEADD(NS, 150000, @Date) AS 'ADD Nano Seconds To A Date'
-- Output ==> 2020-05-20 17:47:12.2731500

--------------------------------------------------------------

-- Subtract Time Units To A Date

-- Subtract Hours to A DateTime
SELECT DATEADD(HH, -1, @Date) AS 'Subtract Hours To A Date'
-- Output ==> 2020-05-20 16:47:12.2730000

-- Subtract Minutes to A DateTime
SELECT DATEADD(MI, -10, @Date) AS 'Subtract Minutes To A Date'
-- Output ==> 2020-05-20 17:37:12.2730000

-- Subtract Seconds to A DateTime
SELECT DATEADD(SS, -1500, @Date) AS 'Subtract Seconds To A Date'
-- Output ==> 2020-05-20 17:22:12.2730000

-- Subtract MilliSeconds to A DateTime
SELECT DATEADD(MS, -1500, @Date) AS 'Subtract MilliSeconds To A Date'
-- Output ==> 2020-05-20 17:47:10.7730000

-- Subtract MicroSeconds to A DateTime
SELECT DATEADD(MCS, -150000, @Date) AS 'Subtract Microseconds To A Date'
-- Output ==> 2020-05-20 17:47:12.1230000

-- Subtract Nano Seconds to A DateTime
SELECT DATEADD(NS, -150000, @Date) AS 'Subtract Nana Seconds To A Date'
-- Output ==> 2020-05-20 17:47:12.2728500

Note: NANOSECOND and MICROSECOND units of time can only be used with SQL Server 2008 and later version of SQL Server.

I hope this article will help you to understand how to add and subtract years, months, weeks, days, hours, minutes, seconds, milliseconds, microseconds, nanoseconds from DateTime in SQL server.

Share your valuable feedback, please post your comment at the bottom of this article. Thank you!

Tagged: SQL Server SQL functions SQL Server Functions Datetime functions Data Conversion SQL DateTime Functions SQL Server DateTime Functions