Managing dates and times is a major aspect of a developer's life with databases. Whether you are counting down a deadline, noting a past due date, or scheduling reports for certain dates, you will need it. SQL Server has one of the most useful built-in date functions you can use: DATEADD().
In this blog post, we will discuss everything you need to know about DATEADD() in a simple way, from basic usage to advanced usage.
What is DATEADD()?
DATEADD() is a built-in SQL Server function that returns a new date by adding (or subtracting) a specified time interval to a date.
DATEADD(datepart, number, date)
Parameters:
datepart—The part of the date to increment (e.g., year, month, day).
number—The number of intervals you want to add (can be negative to subtract).
date—The starting date.
Let's understand with examples:
Suppose you need to add 1 day to a date.
Select DATEADD(DAY,1,GETDATE()) As [10 Days Added]
Select GetDate() As [Today Date]
Similarly, you can subtract days from a date:
Subtract 3 Months from Today:
SELECT DATEADD(month, -3, GETDATE()) AS NewDate;
Select GetDate() As [Today Date]
Add 2 Years to a Specific Date:
SELECT DATEADD(year, 2, GETDATE()) AS NewDate;
Select GetDate() As [Today Date]
Add 5 Hours to Current Time:
SELECT DATEADD(hour, 5, GETDATE()) AS NewTime;
Select GetDate() As [Current Time]
Subtract 15 Minutes from a Time:
SELECT DATEADD(minute, -15, '2025-07-27 08:30:00') AS AdjustedTime;
Calculate End of the Month (using
EOMONTH() + DATEADD()) SELECT DATEADD(day, 1, EOMONTH(GETDATE())) AS FirstDayOfNextMonth;
Using
DATEADD() in WHERE Clauses: -- Get records from the last 7 days
SELECT *
FROM BlogMaster
WHERE CreatedDate >= DATEADD(day, -7, GETDATE());
Tips:
- Data Type Compatibility: Make sure the date argument is a valid datetime, date, or timestamp type.
- Overflow Errors: Extremely large numbers can cause overflow errors.
- Negative Numbers: Use negative values in numbers to subtract dates.
🙏 Thank you for reading!
Thank you for taking the time to read this blog!
If you have any questions or need help with something, feel free to drop a message in the comments or contact section. I’ll get back to you as soon as possible.
Happy Learning! 😊