Date functions — Marketing Cloud SQL reference | Cleon

Date functions in MC SQL look like T-SQL. The traps are timezone (server time is UTC, not yours), month math (DATEADD(month, ...) returns inconsistent results around month boundaries), and wrapping date columns in functions inside WHERE (kills the index). Most date bugs in production come from one of those three.

Official syntax

-- Current timestamp (UTC in MC, NOT your tenant's local time)
SELECT GETDATE() AS NowUtc;

-- Add or subtract a date interval
SELECT DATEADD(day, -90, GETDATE())     AS NinetyDaysAgo;
SELECT DATEADD(hour, 12, GETDATE())     AS TwelveHoursLater;
SELECT DATEADD(month, -3, '2026-05-31') AS RiskyMonthMath; -- see gotcha below

-- Difference between two dates (in unit)
SELECT DATEDIFF(day, LastPurchase, GETDATE())   AS DaysSincePurchase
FROM master_subscribers;

-- Extract part of a date as integer
SELECT
  YEAR(LastPurchase)              AS PurchaseYear,
  MONTH(LastPurchase)             AS PurchaseMonth,
  DAY(LastPurchase)               AS PurchaseDay,
  DATEPART(weekday, LastPurchase) AS DayOfWeek
FROM master_subscribers;

-- Format a date as text via CONVERT (style codes — see Conversion fns)
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Iso8601;   -- 2026-05-07 14:23:11

-- End of month
SELECT EOMONTH(GETDATE()) AS LastDayOfThisMonth;

The supported set across most SFMC tenants:

| Function | What it does | Returns | |---|---|---| | GETDATE() | Current server timestamp (UTC in MC) | DATETIME | | SYSDATETIME() | Same as GETDATE() with sub-second precision | DATETIME2 | | DATEADD(unit, n, date) | Add n of unit (day, month, year, hour, etc.) | DATETIME | | DATEDIFF(unit, start, end) | Difference in unit between two dates | INT | | DATEPART(unit, date) | Extract integer part (year, month, weekday, etc.) | INT | | YEAR(d) / MONTH(d) / DAY(d) | Shorthand for DATEPART | INT | | EOMONTH(d) | Last day of d's month (some editions) | DATE | | DATENAME(unit, d) | Text name of date part (e.g. 'Wednesday') | NVARCHAR | | GETUTCDATE() | Same as GETDATE() in MC (server is UTC) | DATETIME |

DATEADD units commonly used: day, month, year, hour, minute, second, week, quarter. Not all editions support microsecond / nanosecond reliably — stick to the common set.

Reference:

Salesforce Help — Supported T-SQL syntax (functions section) ↗

What survives in production

`GETDATE()` is UTC, not your tenant's local time

MC servers run on UTC. GETDATE() returns UTC, regardless of your Business Unit's configured timezone. If your business rule is "send between 9am and 6pm Buenos Aires time", you have to do the conversion in SQL.

-- AT RISK — uses server (UTC) time, sends arrive at the wrong local hour
WHERE DATEPART(hour, GETDATE()) BETWEEN 9 AND 18;

-- DURABLE — convert to ART (UTC-3) before extracting hour
WHERE DATEPART(hour, DATEADD(hour, -3, GETDATE())) BETWEEN 9 AND 18;

-- BETTER STILL — store the offset in a config DE that's editable
-- without touching SQL (DST-aware, multi-tenant friendly)
WHERE DATEPART(hour, DATEADD(hour, (SELECT UtcOffset FROM de_config_timezone WHERE Locale = 'AR'), GETDATE())) BETWEEN 9 AND 18;

The offset hardcode breaks during daylight-saving transitions. The lookup pattern survives them.

`DATEADD(month, ...)` is unstable around month boundaries

DATEADD(month, -3, '2026-05-31') returns '2026-02-28' (or '2026-02-29' in a leap year). The result depends on the engine's interpretation of "same day three months ago" when the day doesn't exist in the target month. The same query run two days apart on the 31st can return different days.

-- AT RISK — month math, edge cases around year boundaries and short months
WHERE LastPurchase >= DATEADD(month, -3, GETDATE())

-- STABLE — explicit day count, behaves the same every run
WHERE LastPurchase >= DATEADD(day, -90, GETDATE())   -- "last ~3 months"

If the business rule is "last 3 months", translate to a day count once at design time and document it. See gotchas — #8.

Wrapping a date column in a function kills the index

The same rule that hits LOWER(string_col) in WHERE hits date functions: any function applied to the column itself prevents index use. Apply the function to the literal side instead.

-- AT RISK — DATEADD on the column, full scan
WHERE DATEADD(day, 30, LastPurchase) >= GETDATE()

-- DURABLE — transform the literal, the column stays bare
WHERE LastPurchase >= DATEADD(day, -30, GETDATE())

-- AT RISK — YEAR() on the column, full scan
WHERE YEAR(LastPurchase) = 2026

-- DURABLE — bare column on one side, range on the other
WHERE LastPurchase >= '2026-01-01'
  AND LastPurchase <  '2027-01-01'

The first version of each pair returns the same rows but pays the function call on every row in the source. On a 5M-row DE that's the difference between query success and the 30-min timeout.

WATCH OUT

GETDATE() is non-deterministic — every call may return a different value. Don't use it inside an expression you reference more than once in the same query, because each reference can resolve to a slightly different timestamp:

-- AT RISK — two GETDATE() calls, can disagree by a few ms
SELECT
  DATEADD(day, -30, GETDATE())  AS WindowStart,
  DATEADD(day, -1,  GETDATE())  AS WindowEnd;

In a fast query the drift is invisible; in a slow query it can cause off-by-one boundary errors. The defense: capture GETDATE() once into a variable or a CTE-equivalent staging step, then reference the captured value.

Date column types: `DATE` vs `DATETIME` vs `DATETIME2`

A DATE column stores only year-month-day (no time). A DATETIME adds hours-minutes-seconds. A DATETIME2 adds sub-second precision. Joining or comparing across types triggers implicit coercion.

-- AT RISK — comparing DATE column to DATETIME literal, the engine
-- coerces and the comparison may behave unexpectedly at midnight
WHERE LastPurchase = '2026-05-07 00:00:00'

-- DURABLE — match types: if the column is DATE, compare to a date literal
WHERE LastPurchase = '2026-05-07'

-- For DATETIME columns, prefer range comparison over equality
WHERE LastPurchase >= '2026-05-07'
  AND LastPurchase <  '2026-05-08'

When you create a destination Data Extension, pick DATE if you don't need the time — it saves storage and avoids the time-zero comparison trap.

Quick decision

Use day-count windows (DATEADD(day, ...)) instead of month-count when:

The filter drives Send eligibility, journey entry, or any production behavior that has to match exactly across runs.

Convert GETDATE() to local time when:

The query expresses a business rule in local hours (send windows, business-day filters, daily reports).

Keep date columns bare (no function wrap) in WHERE when:

The Data Extension has more than ~100k rows and the query is in a production Activity. Move the function to the literal side instead.

Capture GETDATE() once and reference the variable when:

The query references "now" more than once and the boundaries have to match exactly.

Basics — supported T-SQL subset
WHERE — date functions in filters (and the bare-column rule)
SELECT — date functions in projection
INSERT INTO — date column type matching at the destination
MC SQL gotchas — see #8 (date arithmetic crosses year boundaries)

More function reference pages incoming: Numeric · Conversion · Aggregate · Null Functions.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.

Official syntax

What survives in production

GETDATE() is UTC, not your tenant's local time

DATEADD(month, ...) is unstable around month boundaries

Wrapping a date column in a function kills the index

Date column types: DATE vs DATETIME vs DATETIME2

Quick decision

Related

`GETDATE()` is UTC, not your tenant's local time

`DATEADD(month, ...)` is unstable around month boundaries

Date column types: `DATE` vs `DATETIME` vs `DATETIME2`