SQL DATE_ADD
What is SQL DATEADD?
The SQL DATEADD
function is used to perform date and time arithmetic in SQL. It allows you to add or subtract a specific interval (such as days, months, hours, etc.) to a date or timestamp value. This function is especially useful for calculating future or past dates and adjusting date-related data in SQL queries.
When you would use it
You would use the DATEADD
function when you need to:
-
Date Calculations: Perform calculations involving date and time, such as finding a future date, determining a deadline, or scheduling events.
-
Data Adjustments: Modify date-related data within your database, for instance, updating due dates or delivery times.
-
Temporal Filtering: Filter records based on specific date or time criteria adjusted by adding or subtracting intervals.
Syntax
The syntax for the DATEADD
function may vary slightly depending on the database system, but a general representation is as follows:
DATEADD(interval, number, date)
interval
: The unit of time to add or subtract (e.g., YEAR, MONTH, DAY, HOUR, MINUTE, SECOND).number
: The number of intervals to add or subtract.date
: The date or timestamp value to which you want to apply the calculation.
Parameter values
-
interval
: The unit of time you want to add or subtract, which can be one of the following:YEAR
: YearQUARTER
: QuarterMONTH
: MonthDAY
: DayHOUR
: HourMINUTE
: MinuteSECOND
: Second
-
number
: The number of intervals to add (positive number) or subtract (negative number). -
date
: The date or timestamp value to which you want to apply the calculation.
Example query
Here's an example SQL query that uses the DATEADD
function to calculate the delivery date by adding 7 days to the order date:
SELECT
order_id,
order_date,
DATEADD(DAY, 7, order_date) AS delivery_date
FROM orders;
Example table response
The example query calculates the delivery date by adding 7 days to the "order_date" in the "orders" table. The resulting table response will include the order ID, order date, and calculated delivery date:
order_id | order_date | delivery_date |
---|---|---|
1 | 2023-10-19 | 2023-10-26 |
2 | 2023-09-05 | 2023-09-12 |
3 | 2023-08-15 | 2023-08-22 |
Use cases
-
Date Calculations: To calculate future or past dates, such as scheduling events, setting deadlines, or predicting delivery dates.
-
Data Adjustments: For modifying date-related data within your database, like adjusting due dates or changing the start times of scheduled tasks.
-
Temporal Filtering: For filtering and retrieving records based on specific date or time criteria adjusted by adding or subtracting intervals, like fetching orders placed in the next week.
SQL Languages Availability
The DATEADD
function is available in various SQL-based relational database management systems (RDBMS), but its implementation and syntax may differ among systems. Here are some examples of its availability:
- SQL Server:
DATEADD(interval, number, date)
- MySQL:
DATE_ADD(date, INTERVAL number interval_type)
- PostgreSQL:
date + interval 'number interval_type'
- Oracle Database:
date + INTERVAL number interval_type
- IBM Db2:
DATE(date, number, interval_type)
- SQLite: The
DATEADD
function is not standard SQL, but you can perform similar calculations using date and time functions.
Be sure to consult the documentation of your specific RDBMS for precise usage details and variations.