DATEADD function in SQL Server

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

This article 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 :

  1. 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
  2. number: the amount of time you want to add.
  3. thoigian: the amount of time you want to add numbers .

Note :

  1. 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 .
  2. 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 .
  3. If the number parameter is in decimal , DATEADD will only use the integer part (remove the decimal part).
  4. 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 example

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'

Previous article: DATEDIFF function in SQL Server

Next article: DATENAME function in SQL Server

4 ★ | 1 Vote