DATEADD Function in SQL Server

Introduction

In this article, I would like to show the Dateadd Function in SQL Server. The Dateadd Function is used to add and subtract dates to an existing date and time value. So let's have a look at a practical example of how to use a Dateadd function in SQL Server 2012. The example is developed in SQL Server 2012 using the SQL Server Management Studio.

DATEADD Function in SQL Server

The DATEADD function is used to perform date and time operations. The DATEADD function requires three arguments. The syntax of the DATEADD built-in date function is as follows:

DATEADD (<Part of date>, <Number>, <Date>)


All three parameters of the DATEADD function are required. The <Part of date> parameter is the part of the date parameter to which an integer number is added. The <number> is an expression that can be resolved to an INT data type that is added to a <datepart> of the <date> parameter.

The DATEADD Function With Adding Days

The GetDate function will return the current date.

Example

--Current Datetime
Select  GETDATE() as [CurrentDate]

Go

--To add 10 days to date and time the function would be:
SELECT DATEADD(DAY, 10, GETDATE()) AS [10 days adding with Date]

Output

DateAdd-Function-in-SQL-Server.jpg

Use the DATEADD Function with an existing date and time value.

SELECT DATEADD(DAY, 10, '12/5/2009') AS [10 days adding with Date]

Output

DateAdd-Function-with-existing-datetime-in-SQL-Server.jpg

The DATEADD Function to Subtract Days.

SELECT DATEADD(DAY, -10, GETDATE()) AS [10 days subtracting from current Date]

Output

DateAdd-Function-with-subtracting-in-SQL-Server.jpg

Nesting DATEADD Function

Declare @Date DATE = '2010-03-21'

SELECT DATEADD(MONTH, 2,  @Date)
SELECT DATEADD(MONTH, 2, DATEADD(MONTH, 1, @Date))

Output

Nesting-DateAdd-Function-in-SQL-Server.jpg


Similar Articles