SQL DATEADD()
The DATEADD()
function is a powerful date manipulation function in SQL Server that adds or subtracts a specified time interval to a date. This function enables precise date arithmetic by allowing users to modify date and time values in various increments such as years, months, days, hours, minutes, seconds, and even milliseconds.
DATEADD()
is commonly used in a wide range of database applications that require date calculations, such as determining future dates for appointments, calculating age-based metrics, setting expiration dates, scheduling recurring events, or performing time-series analysis. Its flexibility makes it essential for any application that needs to perform date-time arithmetic operations in SQL Server.
Syntax
DATEADD(interval, number, date)
Parameters:
interval
: The date part to which the number will be added. Acceptable values include:year
,yyyy
,yy
- Yearquarter
,qq
,q
- Quartermonth
,mm
,m
- Monthdayofyear
,dy
,y
- Day of the yearday
,dd
,d
- Dayweek
,wk
,ww
- Weekweekday
,dw
,w
- Weekdayhour
,hh
- Hourminute
,mi
,n
- Minutesecond
,ss
,s
- Secondmillisecond
,ms
- Millisecondmicrosecond
,mcs
- Microsecondnanosecond
,ns
- Nanosecond
number
: The value to add to the specified interval. This can be positive to add time or negative to subtract time.date
: The date value to which the interval will be added. This can be a date literal, a column containing date values, or a date returned by another function.
Return value:
The DATEADD()
function returns a value of the same data type as the input date
expression.
Example 1: Basic Usage of the DATEADD()
function
This example demonstrates how to add different time intervals to a date using the DATEADD()
function:
-- Add 1 year to a dateSELECT DATEADD(year, 1, '2023-05-21') AS OneYearLater;-- Add 3 months to a dateSELECT DATEADD(month, 3, '2023-05-21') AS ThreeMonthsLater;-- Add 14 days to a dateSELECT DATEADD(day, 14, '2023-05-21') AS TwoWeeksLater;-- Subtract 6 hours from a datetimeSELECT DATEADD(hour, -6, '2023-05-21 12:00:00') AS SixHoursEarlier;
The output of this code will be:
OneYearLater | ThreeMonthsLater | TwoWeeksLater | SixHoursEarlier |
---|---|---|---|
2024-05-21 | 2023-08-21 | 2023-06-04 | 2023-05-21 06:00:00.000 |
This example shows how DATEADD()
can perform a variety of date calculations by simply changing the interval parameter and the number value.
Note: Negative numbers decrease the date rather than increase it.
Example 2: Calculating Due Dates
This example demonstrates how DATEADD()
can be used to calculate payment due dates in a business scenario:
-- Create a sample invoice tableCREATE TABLE #Invoices (InvoiceID INT PRIMARY KEY,CustomerID INT,InvoiceDate DATE,Amount DECIMAL(10, 2));-- Insert sample dataINSERT INTO #Invoices (InvoiceID, CustomerID, InvoiceDate, Amount)VALUES(1001, 5, '2023-05-01', 1250.00),(1002, 8, '2023-05-10', 875.50),(1003, 12, '2023-05-15', 3420.75);-- Calculate 30-day payment due dates for each invoiceSELECTInvoiceID,CustomerID,InvoiceDate,DATEADD(day, 30, InvoiceDate) AS DueDate,AmountFROM#Invoices;-- Clean upDROP TABLE #Invoices;
The output of this code will be:
InvoiceID | CustomerID | InvoiceDate | DueDate | Amount |
---|---|---|---|---|
1001 | 5 | 2023-05-01 | 2023-05-31 | 1250.00 |
1002 | 8 | 2023-05-10 | 2023-06-09 | 875.50 |
1003 | 12 | 2023-05-15 | 2023-06-14 | 3420.75 |
This example showcases a practical application of DATEADD()
in a business context where invoices typically have a payment period of 30 days. The function makes it easy to consistently calculate these due dates.
Example 3: Working with Variable Intervals
This example demonstrates how to use DATEADD()
with variables to create flexible date calculations:
-- Declare variables for different parts of the calculationDECLARE @StartDate DATETIME = '2023-01-15 08:30:00';DECLARE @MonthsToAdd INT = 6;DECLARE @DaysToAdd INT = 10;DECLARE @HoursToAdd INT = 3;-- Calculate the result date by applying multiple DATEADD() operationsSELECT@StartDate AS StartDate,DATEADD(month, @MonthsToAdd, @StartDate) AS AfterAddingMonths,DATEADD(day, @DaysToAdd,DATEADD(month, @MonthsToAdd, @StartDate)) AS AfterAddingMonthsAndDays,DATEADD(hour, @HoursToAdd,DATEADD(day, @DaysToAdd,DATEADD(month, @MonthsToAdd, @StartDate))) AS FinalDateTime;
The output generated by this code will be:
StartDate | AfterAddingMonths | AfterAddingMonthsAndDays | FinalDateTime |
---|---|---|---|
2023-01-15 08:30:00.000 | 2023-07-15 08:30:00.000 | 2023-07-25 08:30:00.000 | 2023-07-25 11:30:00.000 |
This example shows how DATEADD()
can be nested and used with variables to create complex date calculations. This approach is particularly useful in stored procedures or dynamic SQL where the amount to add may vary based on different parameters or business rules.
Frequently Asked Questions
1. What happens when DATEADD()
adds months or years that would create an invalid date?
When DATEADD()
would create an invalid date (like February 30), SQL Server automatically adjusts to the last valid date of the month. For example, adding one month to January 31 results in February 28 (or 29 in leap years).
-- Example of month-end adjustmentSELECT DATEADD(month, 1, '2023-01-31') AS OneMonthLater; -- Returns 2023-02-28
2. How does DATEADD()
handle different date formats?
DATEADD()
works with SQL Server’s standard date formats. For string literals, it’s best practice to use formats that are unambiguous, such as ‘YYYY-MM-DD’ (ISO format), to ensure consistent results regardless of server settings.
3. What is the maximum range that DATEADD()
can handle?
DATEADD()
can handle dates within SQL Server’s datetime range, from January 1, 1753, to December 31, 9999. For datetime2, the range is January 1, 0001 to December 31, 9999.
Contribute to Docs
- Learn more about how to get involved.
- Edit this page on GitHub to fix an error or make an improvement.
- Submit feedback to let us know how we can improve Docs.
Learn SQL on Codecademy
- Skill path
Analyze Data with SQL
Learn to analyze data with SQL and prepare for technical interviews.Includes 9 CoursesWith CertificateBeginner Friendly17 hours - Free course
Learn SQL
In this SQL course, you'll learn how to manage large datasets and analyze real data using the standard data management language.Beginner Friendly5 hours