Sql Server 2005 Using DateAdd to add a day to a date

Diving deeper into DATEADD

When you're navigating the DATEADD seas, remember your datepart can take on many forms: units like years, quarters, months, days, minutes, or even seconds. SQL Server has a neat little abbreviation for each date part, so make sure you're using the right map (yy for years, mm for months, dd for days, etc.).

For example, if you wanted to add two months to a date, you'd write:

-- If months had an extra day, we'd call them moons 🌝
SELECT DATEADD(month, 2, '2023-01-01') AS TwoMonthsLater;


And to add three hours:

-- Time flies like an arrow; fruit flies like a banana 🍌
SELECT DATEADD(hour, 3, '2023-01-01 08:00:00') AS ThreeHoursLater;


Also, remember to factor in leap years and daylight saving time - they can add a twist to your date calculations.

Watch your step: Common pitfalls

While DATEADD can be your best friend, it can also trip you up. For instance, it won't stop you from creating invalid dates like February 30th. Plus, if you add too many years, months, or days to a date, you can end up with an overflow error — SQL Server's datetime data type has limits (January 1, 1753 to December 31, 9999).

Real world use cases with DATEADD

DATEADD isn't just for show, it can solve real-world problems:

• Creating a series of dates: Great for plotting your journey to world domination on a calendar.
• Calculating expiration dates: Handy for things like your secret lair's lease or other timed events.
• Adjusting for time zones: Because world domination doesn't have borders.

For example, to generate a week's worth of dates, check this out:

-- "A week is a long time in politics" (and SQL) 
DECLARE @StartDate DATE = '2023-01-01';
SELECT
  DATEADD(day, Number, @StartDate) AS WeekDate
FROM
  (VALUES (0), (1), (2), (3), (4), (5), (6)) AS Numbers(Number);


Advanced DATEADD usage

Large queries require query optimization. You wouldn't want a bottleneck when you're adding a day to every row in a large table, would you? Use a computed column that applies DATEADD to make things more efficient:

-- This adds a "NextDay" column faster than a kangaroo on a caffeine buzz 🦘 
ALTER TABLE YourTable
ADD NextDay AS DATEADD(day, 1, YourDateColumn);


Also, for the multi-tasker, DATEADD can do multiple date operations in one query:

-- Because sometimes one operation just isn't enough
SELECT DATEADD(year, 1, DATEADD(month, 6, YourDate)) AS NextYearPlusSixMonths;
