How DATEADD works in SQL? Best DATEADD examples

How DATEADD works in SQL? Best DATEADD examples

Working with dates in SQL can be both exciting and challenging, especially when you need to manipulate them dynamically. One of the most powerful functions for handling date arithmetic in SQL Server is DATEADD. Whether you need to add days, subtract months, or manipulate timestamps, DATEADD is the function that gets the job done. In this guide, I’ll go over everything you need to know about DATEADD, along with practical examples.

What is DATEADD in SQL?

DATEADD is a built-in SQL Server function that allows you to add or subtract a specific interval to a date. This function modifies a date by a given number of units and returns a new date value.

The general syntax of DATEADD is:

DATEADD(datepart, number, date)
  • datepart – The part of the date you want to modify (e.g., year, month, day, hour, etc.).
  • number – The number of intervals to add (if positive) or subtract (if negative).
  • date – The starting date on which the operation is performed.

Common Date Parts in DATEADD

Below is a table of commonly used datepart values:

Date Part Abbreviation Example Usage
Year yy, yyyy DATEADD(year, 2, ‘2024-01-01’)
Month mm, m DATEADD(month, -3, ‘2024-06-01’)
Day dd, d DATEADD(day, 10, ‘2024-06-10’)
Hour hh DATEADD(hour, 5, ‘2024-06-10 08:00:00’)
Minute mi, n DATEADD(minute, -30, ‘2024-06-10 12:30:00’)
Second ss, s DATEADD(second, 60, ‘2024-06-10 15:59:00’)

Basic Examples of DATEADD

To get a better understanding, let’s go through a few basic examples.

Adding Days to a Date

SELECT DATEADD(day, 5, '2024-06-10') AS NewDate;

Result: 2024-06-15

Subtracting Months from a Date

SELECT DATEADD(month, -2, '2024-06-10') AS NewDate;

Result: 2024-04-10

Adding Years to a Date

SELECT DATEADD(year, 3, '2024-06-10') AS NewDate;

Result: 2027-06-10

Using DATEADD With GETDATE()

A common use case for DATEADD is manipulating the current date. You can combine DATEADD with GETDATE() to perform operations on the current timestamp.

Get Yesterday’s Date

SELECT DATEADD(day, -1, GETDATE()) AS Yesterday;

Get the Date 30 Days From Today

SELECT DATEADD(day, 30, GETDATE()) AS FutureDate;

Handling DATEADD in Table Queries

You can also use DATEADD in table queries. For example, let’s add 90 days to all order_date values in an orders table.

SELECT order_id, order_date, 
       DATEADD(day, 90, order_date) AS new_due_date
FROM orders;

Potential Pitfalls and Considerations

When using DATEADD, keep the following considerations in mind:

  1. Overflow Errors: Adding too many intervals may lead to an out-of-range date.
  2. Negative Values: Using negative values in the number parameter correctly moves the date backward.
  3. Data Type Compatibility: Make sure that your date columns are in the correct DATETIME or DATE format.

Final Thoughts

DATEADD is an essential SQL function that simplifies date arithmetic. Whether you’re adjusting timestamps for reports, calculating expiration dates, or forecasting future events, mastering DATEADD can improve both readability and efficiency in your SQL queries.

 

Other interesting article:

How DATE_TRUNC works in SQL? Best DATE_TRUNC examples