DATEADD() in SQL
Adds or subtracts a specified time interval from a date value.
Syntax
Return type
DATEADD() Function Example
What is DATEADD() in SQL?
The DATEADD() function in SQL is used to add or subtract a specific time interval (such as days, months, or years) to a given date value. It is commonly used for date calculations, scheduling tasks, and adjusting timestamps dynamically.
This function is primarily available in SQL Server. Other databases use different equivalents:
MySQL & PostgreSQL:
DATE_ADD()andDATE_SUB().Oracle: Uses direct date arithmetic (
SYSDATE + INTERVAL '1' DAY).
The DATEADD() function requires three parameters:
Date part (e.g.,
YEAR,MONTH,DAY,HOUR).Value to add/subtract (negative for subtraction).
Starting date.
It is widely used in reporting, forecasting, and calculating expiration or due dates.
Parameters:
interval_type: Unit of time (year, month, day, hour, minute, second)
interval_value: Number of intervals to add (negative for subtraction)
date_expression: Starting date value
Example Use Cases:
-- Calculate due date
-- Find orders from last week
-- Schedule future maintenance
Notes:
Behavior: Returns NULL if any parameter is NULL
Performance Considerations: Date calculations may cross calendar boundaries
Version Info: Syntax varies significantly by DBMS
Deprecated/Recommended Alternatives: None
Error Handling:
Error: Returns NULL or error for invalid dates
Recommendation: Validate input dates before calculation
Supported Databases:
DBMS
Function / Syntax
Example
Behavior with NULL
MySQL
DATE_ADD(), + INTERVAL
DATE_ADD(date, INTERVAL 1 DAY)
Returns NULL
PostgreSQL
+ INTERVAL
date + INTERVAL '1 day'
Returns NULL
SQL Server
DATEADD()
DATEADD(day, 1, date)
Returns NULL
SQLite
datetime(), julianday()
date(date, '+1 day')
Returns NULL
BigQuery
DATE_ADD(), DATETIME_ADD()
DATE_ADD(date, INTERVAL 1 DAY)
Returns NULL
Snowflake
DATEADD()
DATEADD(day, 1, date)
Returns NULL
Athena
DATE_ADD()
DATE_ADD(day, 1, date)
Returns NULL
Related Functions:
DATEDIFF()
Calculates the difference between two dates in specified interval units.
GETDATE()
Returns the current date and time of the database server.
EOMONTH()
Returns the last day of the month for a specified date with an optional month offset.
DATEFROMPARTS()
Creates a date value from specified year, month, and day integers.
