SQL > SQL Date Functions > Dateadd Function

The DATEADD function is used to add an interval to a date. This function is available in SQL Server.

Syntax

The syntax for the DATEADD function is as follows:

DATEADD (datepart, number, expression)

where the data type of <expression> is some type of date, time, or datetime. <number> is an integer (can be positive or negative). <datepart> can be one of the following:

 datepart  abbreviation 
 year  yy, yyyy 
 quarter  qq, q 
 month  mm, m 
 dayofyear  dy, y 
 day  dd, d 
 week  wk, ww 
 hour  hh 
 minute  mi, n 
 second  ss, s 
 millisecond  ms 
 microsecond  mcs 
 nanosecond  ns 
 TZoffset  tz 
 ISO_WEEK  isowk, isoww 

The result returned has the same data type as <expression>.

Examples

Below we show several examples of the output from applying the DATEADD function.

Example 1

The SQL statement,

SELECT DATEADD (day, 10, '2000-01-05 00:05:00.000');

produces the following result:

'2000-01-15 00:05:00.000'

In this example, we are adding 10 days to the original datetime.

Example 2

The SQL statement,

SELECT DATEADD (hour, -10, '2000-01-05 00:05:00.000');

produces the following result:

'2000-01-04 14:05:00.000'

In this example, we are subtracting 10 hours from the original datetime.

Example 3

The SQL statement,

SELECT DATEADD (mm, 1, '2000-01-05 00:05:00.000');

produces the following result:

'2000-02-15 00:05:00.000'

In this example, we use the abbreviation for "month" to add one month to the original datetime.

List of SQL Date Functions

Function NameDescription
 DATEADD  Adds an interval to a date value in SQL Server. 
 DATEDIFF  Calculates the difference between two dates in MySQL and SQL Server. 
 DATEPART  Extracts a specific part of a date/time value in SQL Server. 
 GETDATE  Retrieves database time in SQL Server. 
 SYSDATE  Retrieves database time in Oracle and MySQL. 
 EXTRACT  Retrieves a certain component of a date or timestamp value. 

Next: SQL DATEDIFF

This page was last updated on June 05, 2022.




Copyright © 2022   1keydata.com   All Rights Reserved     Privacy Policy     About   Contact