DATEADD Function in SQL Server: What You Need to Know

DATEADD Function in SQL Server is easier to understand when the core ideas are paired with practical examples. The sections below explain the topic clearly, highlight useful steps, and point out details that can prevent common errors.

This guide will show you in detail how to use the DATEADD () datetime processing function in SQL Server with specific syntax and examples to better visualize and capture functions.

Describe

The DATEADD function in SQL Server returns a new time value when it is added a specified time period.

Syntax

To use the DATEADD function in SQL Server, we use the following syntax:

 DATEADD(dangthoigian, number, thoigian)

Parameters :

dangthoigian: the type of time used to calculate addition to thoigian. It may be one of the following values: Value Explanation year, yyyy, yy Yearquarter, qq, qQu�month, mm, mThdaydayofyearDate in daysday, dy, yYdayweek, ww, wkTuwwdayday, dw, w Day of the day, hhGinminute, mi, nPh�tsecond, ss, s Millisecond, msMis second
number: the amount of time you want to add.
thoigian: the amount of time you want to add numbers .

Note :

If the number parameter > 0 , DATEADD function will understand that this is the number of times to be incremented and added to the thoigian parameter .
If the number <0 parameter, DATEADD function will understand this is the number of times you want to reduce and subtract from the thoigian parameter .
If the number parameter is in decimal , DATEADD will only use the integer part (remove the decimal part).
DATEADD can be used in later versions of SQL Server: SQL Server 2017, SQL Server 2016, SQL Server 2014, SQL Server 2012, SQL Server 2008 R2, SQL Server 2008, and SQL Server 2005.

For instance,

Take a look and explore some examples of DATEADD functions in SQL Server.

 SELECT DATEADD(year, 1, '2019/04/28'); 
 Result: '2020-04-28 00:00:00.000' 
 
 SELECT DATEADD(yyyy, 1, '2019/04/28'); 
 Result: '2020-04-28 00:00:00.000' 
 
 SELECT DATEADD(yy, 1, '2019/04/28'); 
 Result: '2020-04-28 00:00:00.000' 
 
 SELECT DATEADD(year, -1, '2019/04/28'); 
 Result: '2018-04-28 00:00:00.000' 
 
 SELECT DATEADD(month, 1, '2019/04/28'); 
 Result: '2019-05-28 00:00:00.000' 
 
 SELECT DATEADD(month, -1, '2019/04/28'); 
 Result: '2019-03-28 00:00:00.000' 
 
 SELECT DATEADD(day, 1, '2019/04/28'); 
 Result: '2019-04-29 00:00:00.000' 
 
 SELECT DATEADD(day, -1, '2019/04/28'); 
 Result: '2019-04-27 00:00:00.000'