SFMC SQL Date Functions: DATEADD, DATEDIFF and Time Zones

Date logic is where many SFMC audience queries quietly go wrong. A "last 30 days" filter that is off by a few hours, or a "90 days inactive" rule that counts the wrong day, sends the wrong people into a campaign and nobody sees an error.

The short answer: SFMC SQL date functions follow SQL Server syntax. Use GETDATE() for the current moment, DATEADD() to move a date forward or back, DATEDIFF() to count the gap between two dates, and AT TIME ZONE when your stored dates are not in the same time zone as the server.

This guide walks through the patterns practitioners use most, with copy-ready SQL, the time zone trap that causes silent errors, and a quick reference table. If you would rather skip the syntax entirely, you can also generate SFMC SQL from plain English.

‍

How SFMC SQL date functions work

‍

GETDATE, DATEADD and DATEDIFF in one minute

Query activities in Automation Studio run SELECT statements written in SQL Server syntax, so the familiar T-SQL date toolkit applies. Three functions cover most audience logic.

• GETDATE() returns the current date and time at the moment the query runs.
• DATEADD(unit, number, date) returns a date shifted by a number of units. Use a negative number to go back in time, for example DATEADD(DAY, -30, GETDATE()).
• DATEDIFF(unit, start, end) returns how many unit boundaries lie between two dates, for example the number of days between a purchase and today.

‍

Units include DAY, HOUR, MONTH and YEAR. Keep in mind that SFMC queries are SELECT only, so there are no variables: every date expression has to live inside the query itself.

‍

Which time zone does GETDATE use?

This is the question behind most date bugs. SFMC practitioners report that GETDATE() returns the server time in Central America Standard Time, which is UTC-6 all year because that zone does not observe daylight saving.

Date columns in data views such as _Open and _Click are commonly described as following the same convention. Dates you import yourself may be stored in your local zone instead. Before you rely on any of this, run a one-line test query against your own account and compare the result with the current UTC time.

‍

Pattern 1: engaged in the last 30 days with DATEADD

‍

The most common request is a rolling window: everyone who did something in the last N days. Here is a clean version for opens, built on the _Open data view.

SELECT
  o.SubscriberKey,
  MAX(o.EventDate) AS LastOpenDate
FROM _Open o
WHERE o.EventDate >= DATEADD(DAY, -30, GETDATE())
GROUP BY o.SubscriberKey

Here is what each part does.

• FROM _Open o reads every open event your account has recorded, within data view retention.
• The WHERE clause keeps only events from the last 30 days, counted back from the moment the query runs.
• MAX(o.EventDate) returns the most recent open per subscriber, so the result has one row per person.
• GROUP BY o.SubscriberKey is what makes that one-row-per-person shape possible.

‍

Target data extension: Opened_Last_30_Days, with SubscriberKey as the primary key. Update type: Overwrite, so the audience is rebuilt fresh on every run. If you need the full list of columns available, see our guide to querying data views in SFMC.

‍

Keep the function on the right side of the comparison

Notice that DATEADD() is applied to GETDATE(), not to the column. Writing EventDate >= DATEADD(...) keeps the logic readable and avoids transforming every row in a large data view. Wrapping the column in a function is a habit worth dropping.

‍

Pattern 2: days since last purchase with DATEDIFF

‍

For win-back and lapsed-customer audiences, you often start from a customer table that holds a last purchase date. DATEDIFF gives you a column you can also use for sorting priorities later.

SELECT
  c.SubscriberKey,
  c.EmailAddress,
  DATEDIFF(DAY, c.LastPurchaseDate, GETDATE()) AS DaysSincePurchase
FROM Customers_Master c
WHERE c.LastPurchaseDate < DATEADD(DAY, -90, GETDATE())

Line by line, it works like this.

• FROM Customers_Master c uses a generic customer data extension with one row per person.
• DATEDIFF(DAY, ...) AS DaysSincePurchase adds a number you can reuse in the data extension, for example to split 90 to 180 days from 180 plus.
• The WHERE clause keeps customers whose last purchase is older than 90 days.

‍

Target data extension: Lapsed_90_Days, primary key SubscriberKey, update type Overwrite. For the full strategy behind this audience, read how to build a reactivation audience in SFMC.

‍

DATEDIFF counts boundaries, not elapsed time

DATEDIFF(DAY, '2026-10-15 23:59', '2026-10-16 00:01') returns 1, even though only two minutes passed, because one midnight boundary was crossed. That is why the WHERE clause above compares against DATEADD() instead of testing DATEDIFF directly: the rolling window stays exact to the time of day.

Use DATEDIFF for display columns and calendar-day logic. Use DATEADD for rolling windows.

‍

Fix SFMC SQL date and time zone mismatches

‍

The mismatch that silently returns the wrong rows

Suppose an order table stores timestamps in Paris local time, because your import process wrote them that way. If you compare those values with GETDATE(), you are comparing two different clocks. The query runs, returns rows, and shows no error, but the window is shifted by several hours.

The fix is to convert the server time into the zone of your stored data before comparing. SQL Server's AT TIME ZONE clause does this, and Microsoft documents the syntax and zone names in its AT TIME ZONE reference.

SELECT
  o.OrderID,
  o.SubscriberKey,
  o.OrderDate
FROM Orders_Paris o
WHERE o.OrderDate >= CONVERT(DATETIME,
  DATEADD(HOUR, -2, GETDATE())
  AT TIME ZONE 'Central America Standard Time'
  AT TIME ZONE 'Romance Standard Time')

Here is how to read it.

• DATEADD(HOUR, -2, GETDATE()) is the start of the window: two hours ago on the server clock.
• The first AT TIME ZONE tells SQL Server which zone that value is really in.
• The second AT TIME ZONE converts it to Romance Standard Time, the Windows name for the zone covering Paris, Brussels and Madrid.
• CONVERT(DATETIME, ...) removes the offset so the result compares cleanly with a plain datetime column.

‍

Target data extension: Recent_Orders_Paris, primary key OrderID, update type Overwrite. Test it on a small window first and confirm the boundary matches a row you can check by hand.

‍

Let the zone name handle daylight saving

Never hard-code "minus 1 hour" or "minus 2 hours" to shift between zones. The offset between UTC-6 (no daylight saving) and a European zone changes twice a year. Using a named zone means SQL Server applies the right offset for the date in question.

‍

Quick reference: copy these date patterns

‍

Goal	Pattern
Last 30 days	col >= DATEADD(DAY, -30, GETDATE())
Older than 90 days	col < DATEADD(DAY, -90, GETDATE())
Days since an event	DATEDIFF(DAY, col, GETDATE())
Start of today (server clock)	DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Yesterday only	col >= DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)) AND col < DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()), 0)
Next 7 days (for example birthdays)	col >= GETDATE() AND col < DATEADD(DAY, 7, GETDATE())

‍

A short checklist before you schedule any date-based query:

1. Confirm which time zone your date column is stored in.
2. Decide whether you need a rolling window (DATEADD) or calendar days (DATEDIFF).
3. Run it once on a small range and check the first and last rows by hand.
4. Set the target data extension primary key and choose Overwrite, Update or Append on purpose.

‍

Common pitfalls with SFMC SQL date logic

‍

• Text date fields: if a date arrives as text from an import, convert it with CONVERT(DATETIME, field) before comparing, and check for blank values that would make the conversion fail.
• Midnight cut-offs: a rule such as "yesterday" needs both a start and an end boundary, otherwise you pull in today as well.
• Run time versus build time: GETDATE() is evaluated when the query runs, not when you wrote it, so a query scheduled at night can see a different calendar day than you expect.
• Engagement windows: the same logic powers exclusion rules, as covered in our post on finding unengaged contacts in SFMC.

‍

FAQ

‍

What time zone does GETDATE() return in SFMC?

Practitioners report that it returns server time in Central America Standard Time, which is UTC-6 with no daylight saving. Always verify on your own account by comparing the output with UTC.

‍

How do I get the last 30 days in SFMC SQL?

Compare your date column with DATEADD(DAY, -30, GETDATE()) using greater than or equal. This gives a rolling window anchored to the moment the query runs.

‍

What is the difference between DATEADD and DATEDIFF?

DATEADD returns a new date by shifting an existing one. DATEDIFF returns a number: how many unit boundaries lie between two dates. Use DATEADD for windows and DATEDIFF for counts.

‍

Why does my date query return the wrong people without an error?

The usual cause is comparing dates stored in one time zone with GETDATE() in another. Convert with AT TIME ZONE so both sides use the same clock.

‍

Build date-based audiences in seconds with QAiry

‍

Date windows are exactly the kind of detail that is easy to get slightly wrong by hand. QAiry turns a plain-language request such as subscribers who opened in the last 30 days but not in the last 7 into SFMC SQL and a Data Extension, installed as an app in your own SFMC.

You can try QAiry free or watch a product demo to see how it handles dates and data views.