Salesforce Marketing Cloud - SQL Features & Tips Part 1
Creating database queries with SQL is an everyday job in Salesforce Marketing Cloud (SFMC). This makes it easy to retrieve, combine or filter data from various data extensions and data views. The use of SQL in SFMC offers powerful options for data processing and analysis, but also has some special features that users should be aware of.
SQL in Salesforce Marketing Cloud — No mistakes, but still wrong results?
In addition technical and functional limitations of SQL in Marketing Cloud It can happen that queries are executed without errors because the syntax is correct, but still do not produce the desired or plausible results. This discrepancy often goes unnoticed and can lead to unexpected results. We would like to present two such “traps” and give tips on how to avoid them.
Case 1: Time zones for SQL queries in Salesforce Marketing Cloud
Dates are often used to narrow down the population of data extensions or data views to a relevant area. A common condition looks something like this:
WHERE
CONVERT(DATETIME, SubscriptionDate) > DATEADD(HOUR, -2, GETDATE())But be careful: When comparing two dates, there is something important to consider — the time zone!
Why does my SQL database query return incorrect results despite the correct syntax?
In our example, the “SubscriptionDate” field is queried from a data extension to filter out contacts who have subscribed to a newsletter within the last two hours. The DATEADD (HOUR, -2, GETDATE ()) part uses the GETDATE () function to determine the current date and time. Two hours are then subtracted from this.
We therefore compare a date that is stored in a data extension with a date that was generated by a function. It is important to know here: SQL functions such as GETDATE () and TODAY () always return the result in the “Central America Standard Time” (CAST) time zone. The same applies to dates in data views. This can result in queries comparing two dates that are not in the same time zone.
Resolve time zone differences in SQL queries in Marketing Cloud
First, you need to know in which time zone the “SubscriptionDate” field in the data extension is stored. This may vary depending on where the field comes from. For example, if the field is imported into SFMC via API, it may well be that you have the field in your regional time zone. Let's assume that the “SubscriptionDate” field is saved in your regional time zone, e.g. in “Central European Standard Time” (CEST), as it applies in Germany. Because CAST does not observe a time change, CAST is 8 hours behind CEST in summer time and 7 hours behind CEST during winter time. If you run the above query at 2:30 PM CEST (in summer), you would get all subscribers that have arrived since 4:30 AM CEST (i.e. over the last 10 hours: the desired two-hour span plus an 8-hour time difference). The desired result — the contacts who have subscribed within the last two hours — is therefore not achieved. You will not receive an error message because there is no logical or syntax error.
The solution: using AT TIME ZONE in SQL queries
The AT TIME ZONE function allows you to convert dates from your current time zone to another. The adjustment in our example would then look like this:
CONVERT(DATETIME, SubscriptionDate) > DATEADD(HOUR, -2, GETDATE()) AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time'“SubscriptionDate” remains unchanged because it is already in the correct time zone. The right side of the equation is adjusted as follows: The first “AT TIME ZONE” declaration determines which time zone the date is currently in (CAST). The second statement specifies which time zone the date should be converted to (CEST). This ensures that the information on both sides of the equation uses the same time zone.
We hope you found this tip for adjusting time zones useful when querying and comparing data. In the next part, we'll take a closer look at the “UNION” SQL function and explain what you should pay particular attention to when using them in the Marketing Cloud.
