Dateadd SQL: Manipulating Dates in Queries

by | Blog

Ever needed to make magic happen with dates in an SQL query? Like finding yesterday’s sales or next month’s subscription renewals? Well, you’re in luck. SQL gives us a fun little function called DATEADD that makes date manipulation super easy.

TL;DR

SQL’s DATEADD function lets you add or subtract time from a date. Want to find a date 7 days from now? Use DATEADD(day, 7, your_date_column). It’s part calculator, part time travel. You’ll use it all the time for reports, reminders, and trends!

What’s the Big Deal About DATEADD?

Dates are everywhere in data. You have sign-up dates, delivery dates, meeting dates — you name it. But what happens when you need to find something like:

  • What will the delivery date be 5 days after the order?
  • Which users signed up in the last 30 days?
  • What was our revenue 3 months ago?

That’s where DATEADD shines.

How DATEADD Works

The syntax is simple:


DATEADD(datepart, number, date)

Let’s break that down:

  • datepart: This is the unit of time you want to work with — like day, month, year, hour, etc.
  • number: How many units to add (or subtract if it’s negative).
  • date: This is the starting date you want to change.

So, if you want to add 5 days to today’s date:


SELECT DATEADD(day, 5, GETDATE())

GETDATE() gives you the current date and time. Try it in your SQL editor and see the magic!

Common Time Units in DATEADD

Here are some of the most used time units, also called dateparts:

  • year: or yy
  • quarter: or qq
  • month: or mm
  • day: or dd
  • hour: or hh
  • minute: or mi
  • second: or ss

Here’s how to subtract 3 months from today:


SELECT DATEADD(month, -3, GETDATE())

Yes, just use a negative number and DATEADD will go backward in time.

Real-World Examples

1. Find users who signed up in the last 30 days


SELECT * FROM users
WHERE signup_date >= DATEADD(day, -30, GETDATE())

This will show you users who joined in the last 30 days. Perfect for marketing follow-ups!

2. Predict shipping dates


SELECT order_id, order_date, DATEADD(day, 7, order_date) AS ship_date
FROM orders

This adds 7 days to each order date to show estimated shipping dates.

3. Get future reminders


SELECT event_name, DATEADD(week, 2, event_date) AS reminder_date
FROM calendar

A quick way to schedule reminders 2 weeks in advance.

Bonus: Nesting DATEADD with Other Functions

You can also combine DATEADD with other SQL functions like DATEDIFF or GETDATE.

Example: Get the difference AND then add more days:


SELECT DATEADD(day, DATEDIFF(day, '2023-01-01', GETDATE()), '2023-01-01')

It may look weird, but this is like saying, “Start from Jan 1, 2023, go forward the same number of days as we are now into the year.”

Watch Out For These!

Using DATEADD is fun, but here are a few things to keep in mind:

  • If you go past the valid date range, SQL will throw an error.
  • Date formats matter! Always test with your actual data.
  • It works with datetime and date types. It might not work well with strings.

Using DATEADD in Joins or CTEs

You can even use DATEADD in joins or in common table expressions (CTEs) to create more advanced queries. Here’s a cool example:


WITH next_payments AS (
  SELECT user_id, DATEADD(month, 1, subscription_start) AS next_payment_date
  FROM subscriptions
)
SELECT u.name, n.next_payment_date
FROM users u
JOIN next_payments n ON u.id = n.user_id

This query gets each user and their next payment date. How cool is that?

A Trick for End-of-Month Dates

Want to know the last day of the month? Here’s a neat trick using DATEADD:


SELECT DATEADD(day, -1, DATEADD(month, 1, '2024-06-01'))

Explanation: We go to the first day of the next month, then subtract one day. Magic! You can make this dynamic too.

Can I Use It in Update Statements?

Yes! Let’s say you want to expire all trial accounts 14 days after their start date:


UPDATE accounts
SET expiration_date = DATEADD(day, 14, start_date)
WHERE account_type = 'trial'

DATEADD is powerful not just in SELECTs but in UPDATEs and INSERTs too.

Using DATEADD with Parameters

If you’re working in stored procedures or dynamic SQL, you can pass values like:


DECLARE @days INT = 25
SELECT DATEADD(day, @days, '2024-06-01')

Very flexible and great for dynamic reports.

Final Thoughts

If you’re dealing with dates in SQL, DATEADD is one of the first things you should learn. It’s simple and powerful. It keeps your data timely, your queries smart, and your reports on point.

Now you can play around with hours, days, months, and even years like a SQL wizard.

Need to plan ahead? Go back in time? Set reminders? All just a DATEADD away.

Quick Recap 🕒

  • DATEADD(datepart, number, date) is your go-to tool for changing dates.
  • Add or subtract time in just one line of SQL.
  • Works great with GETDATE(), joins, CTEs, and even updates.

Time travel has never been easier — at least in SQL.

Pin It on Pinterest