A cosmetics retailer sends a birthday email every morning, with an offer valid for a week. An automation scheduled for 07:00 Paris time selects the customers born that day and feeds a journey. The query compares the day and month of birth with the server date.
All winter, nothing to report. In early April, several customers write to support: they received their email the day after their birthday. The team rereads the query, runs it by hand mid-morning, and the result is correct. The ticket is closed in error, then reopened the following week.
The cause is not in the query. It sits in the gap between the time the automation fires and the time the SQL engine believes it to be.
Salesforce documentation is explicit on this point: Marketing Cloud system time is North American Central Standard Time, UTC-6, with no switch to daylight saving. The data view reference pages repeat it for their own dates. The gap with Paris, on the other hand, changes twice a year.
| Source | Time zone | Note |
|---|---|---|
GETDATE() | System time, UTC-6 all year | Constant offset from UTC |
GETUTCDATE() | UTC | The same instant, expressed in UTC |
EventDate in the data views | System time, UTC-6 | Rounded to the second for opens and clicks |
| A data extension Date field | No time zone attached | The value is whatever was written, and only your team convention says what it represents |
Paris runs at UTC+1 in winter and UTC+2 in summer, the server at UTC-6 all year. The gap is therefore seven hours from October to March and eight hours the rest of the year, switching on the last Sunday in March and the last Sunday in October. Those two Sundays make the bug appear and disappear, which explains diagnoses that drag on.
In winter, 07:00 in Paris is 06:00 UTC, which is exactly midnight in server time. The system date is the current day and the query lands correctly. In summer, 07:00 in Paris is 05:00 UTC, which is 23:00 the previous day in server time. All season, the query selected the previous day's birthdays.
Rerun at 10:00 by the team, it came back correct, because the server had changed day in the meantime. A query that gives the right answer when you test it by hand and the wrong one when it runs on its own is the classic symptom of a time zone problem.
⚠️ A fixed offset for Paris is wrong half the year. Writing DATEADD(HOUR, 7, o.EventDate) gives Paris time from late October to late March, and one hour short the rest of the time. A fixed offset only holds when moving from system time to UTC, since neither of the two observes daylight saving.
The Query Activity accepts neither variables nor common table expressions. Copying a conversion into every query guarantees that one of the copies will drift. The convention that survives is to work out the reference dates at the top of the automation, in a single row parameter data extension, then join that row everywhere else.
A small switchover table, maintained by hand for a few years ahead, is enough to get the current offset. Each row covers a period and carries the Paris offset from UTC, 1 or 2.
/* Target: Parametres_Date, Overwrite mode, Cle as primary key */
SELECT
'J' AS Cle,
CAST(DATEADD(HOUR, b.DecalageParis, GETUTCDATE()) AS DATE) AS AujourdhuiParis,
DATEADD(HOUR, -(b.DecalageParis + 6),
CAST(DATEADD(DAY, -1,
CAST(DATEADD(HOUR, b.DecalageParis, GETUTCDATE()) AS DATE)
) AS DATETIME)) AS DebutVeilleSys,
DATEADD(HOUR, -(b.DecalageParis + 6),
CAST(CAST(DATEADD(HOUR, b.DecalageParis, GETUTCDATE()) AS DATE)
AS DATETIME)) AS FinVeilleSys
FROM Bascules_Heure AS b
WHERE GETUTCDATE() >= b.DebutUTC
AND GETUTCDATE() < b.FinUTCAujourdhuiParis gives the current date in Paris whatever the run time. The other two columns give midnight of the previous day and midnight of the current day, expressed in system time, ready to be compared with an EventDate. On a switchover day, check the result by hand the first year: the bounds of the previous day belong to the previous offset. A Verification Activity placed just after confirms that the table holds one row, since every query that follows depends on it.
💡 The AT TIME ZONE route still needs testing on your account. SQL Server 2016 offers this syntax to declare the time zone of a date, then convert it with the daylight saving rules of the target zone. Salesforce documentation does not list it among the supported functions, even though community reports show it working inside Query Activities. If it passes validation and runs on your account, it beats the switchover table. Test it before depending on it, and keep the fallback in mind.
With the parameter row in place, a yesterday in Paris window over a data view comes down to one join and two comparisons of system dates.
SELECT
o.SubscriberKey,
o.JobID,
MIN(o.EventDate) AS PremiereOuverture
FROM _Open AS o
INNER JOIN Parametres_Date AS p
ON p.Cle = 'J'
WHERE o.EventDate >= p.DebutVeilleSys
AND o.EventDate < p.FinVeilleSys
GROUP BY o.SubscriberKey, o.JobIDThree choices in this query deserve a word.
EventDate in the WHERE clause. Salesforce recommends avoiding it, because a form such as CAST(o.EventDate AS DATE) = ... prevents index use on a large data view.GROUP BY guarantees one row per pair of keys, even when a subscriber opened the same send several times.On how to read those opens, inflated among part of the audience by automatic image loading, the data views article has the detail.
The audience query joins the parameter row and compares the day and month of birth with the Paris date, not the server date.
SELECT
c.ClientId,
c.Email,
c.Prenom,
DATEDIFF(YEAR, c.DateNaissance, p.AujourdhuiParis)
- CASE WHEN MONTH(c.DateNaissance) > MONTH(p.AujourdhuiParis)
OR (MONTH(c.DateNaissance) = MONTH(p.AujourdhuiParis)
AND DAY(c.DateNaissance) > DAY(p.AujourdhuiParis))
THEN 1 ELSE 0 END AS Age
FROM Clients AS c
INNER JOIN Parametres_Date AS p
ON p.Cle = 'J'
WHERE c.Email IS NOT NULL
AND MONTH(c.DateNaissance) = MONTH(p.AujourdhuiParis)
AND DAY(c.DateNaissance) = DAY(p.AujourdhuiParis)The age calculation corrects DATEDIFF(YEAR, ...), which counts crossings of 1 January rather than completed years. Someone born on 20 December 2000 would turn 26 on 1 January 2026 while they are still 25. On the birthday itself the correction is zero, but the same expression will come back on an offer restricted to adults, where the error stops being cosmetic.
That leaves people born on 29 February, whom this query ignores three years out of four. It is a business rule before it is a SQL rule: some brands handle them on the 28th, others on 1 March. Settle it with marketing, write the rule into the query, and document it. One last performance detail: MONTH and DAY applied to DateNaissance prevent index use. Across several million customers, store the month and day of birth in two numeric fields worked out at import.
Adobe Campaign separates dates stored with a time zone, converted to UTC in the database, from dates stored without one, depending on how the instance is configured, then displays everything in the operator time zone. Marketing Cloud offers no such mechanism in SQL. Data view dates sit at a fixed UTC-6, data extension Date fields carry no time zone at all, and the conversion has to be written into every query. Hence the value of working it out once.
A Campaign workflow scheduler and an automation schedule look much alike, and both fire at a local time. The trap is that the SQL reasons in a different zone from the schedule. That is exactly what produced the retailer's incident.
For every date a query handles, be able to say which time zone it is in. If you cannot answer for a data extension field, the convention is written down nowhere, and now is the moment to set it. The rest follows: one conversion, once, at the top of the automation.
Marketing Cloud system time is North American Central Standard Time, UTC-6, with no switch to daylight saving. GETDATE() returns that time, while GETUTCDATE() returns the same instant expressed in UTC. The gap with Paris is seven hours from October to March and eight hours the rest of the year.
In winter, 07:00 in Paris is 06:00 UTC, which is exactly midnight in server time: the system date is the current day and the query lands correctly. In summer, 07:00 in Paris is 05:00 UTC, which is 23:00 the previous day in server time, so the query selects the previous day's birthdays. The switchover on the last Sunday in March is what makes the problem appear.
No, a fixed offset for Paris is wrong half the year. Writing DATEADD(HOUR, 7, o.EventDate) gives Paris time from late October to late March, and one hour short the rest of the time. A fixed offset only holds when moving from system time to UTC, since neither of the two observes daylight saving.
The Query Activity accepts neither variables nor common table expressions, so the reference dates are worked out once at the top of the automation, in a single row parameter data extension that is then joined everywhere else. The window comes down to one join and two comparisons of system dates, with a half open interval: lower bound included, upper bound excluded. No function is applied to EventDate in the WHERE clause, because a form such as CAST(o.EventDate AS DATE) = ... prevents index use.
DATEDIFF(YEAR, ...) counts crossings of 1 January rather than completed years. Someone born on 20 December 2000 would turn 26 on 1 January 2026 while they are still 25. On the birthday itself the correction is zero, but the same expression will come back on an offer restricted to adults, where the error stops being cosmetic.
I can review your dated queries, your daily windows and your scheduled automations, then hand you a written diagnosis with the corrections to apply.
Describe your situation →