SQL DATEADD | Add Time Intervals to Dates

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 Name	Description
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 October 12, 2024.

SQL DATEADD Function

Syntax

Examples

Example 1

Example 2

Example 3

List of SQL Date Functions

AdBlock Detected!