Take control over dates with built-in SQL functions. Let the query do the job.
Salesforce Marketing Cloud SQL implementation does not support user-defined functions. There are, however, multiple built-in functions that are useful on a day-to-day basis when working with queries.
Here, I will cover only selected date functions that I find most useful for Marketing Automation purposes. They will help you find the current date, calculate a new one or format it to match your needs.
GETDATE & GETUTCDATE
GETDATE function is the most straightforward way to get the current timestamp within your query.
SELECT GETDATE() AS CurrentDate
GETDATE() gets server time. For Salesforce Marketing Cloud, it means Central Standard Time (UTC-6) without changes between standard and daylight savings time. It cannot be changed, even with timezone and culture settings in Setup.
You can get the datetime with appropriate offset information using the
SYSDATETIMEOFFSET() function. It will return the same datetime as
GETDATE() but in a format showcasing the timezone offset:
2020-12-28 15:08:01.7239173 -06:00.
You can see the 24-hour clock format and milliseconds and UTC-6 offset at the end. This last information will be helpful for timezone conversions with the
AT TIME ZONE function.
You can also leverage `TODATETIMEOFFSET' to work with standard
GETUTCDATE function works the same as
GETDATE but returns datetime in the UTC timezone.
SELECT GETUTCDATE() AS CurrentUTCDate
Both return a datetime object (for example,
Oct 30 2020 1:15AM), so no conversion is needed to use other Date Functions on the outcome of this function.
DATEPART function returns the chosen part of the provided date.
WHERE DATEPART(YEAR, DateJoined) = 2020
It outputs an integer, so be sure you use a number when making comparisons. If you need a string output, check out the similar
There are also three shortcuts for the most popular use cases:
WHERE YEAR(DateJoined) = 2020
In the backend, those functions are just wrappers around
DATEPART, so you can use whatever you prefer.
You can reference date parts both by their full name and an abbreviation.
WEEKDAY sounds similar to each other, but they return a bit different value.
DAYOFYEAR will tell you which day of the year it is (f.e.
DAY returns number of the day within the month (f.e.
WEEKDAY returns number of the day within a week (f.e.
2 for Monday).
For 99% of SFMC needs, you will be fine with
MINUTE. Using only those with the full name will make your queries readable and straightforward for everyone.
DATENAME functions return the string value of the specified date part from a given date.
, DATENAME(WEEKDAY, DateJoined) AS DayJoined
It might help quickly cut the date to just an important part (like a year for anniversary personalisation or capturing anniversary bins). However, its standout feature is returning the day's name for a given date, thanks to the
WEEKDAY date part.
WHERE DATENAME(WEEKDAY, DayOfMeeting) IN ('Friday', 'Saturday', 'Sunday')
AND DATEDIFF(DAY, GETDATE(), DayOfMeeting) < 3
It works for months as well:
DATENAME returns a string, you won't be able to do any calculations on the outcome - even if it will contain only digits (like
DATENAME from the
YEAR date part). If you want to perform operations, you must use the
DATEFROMPARTS & DATETIMEFROMPARTS
DATEFROMPARTS lets you create a date value from three integers representing a year, month and day.
, DATEFROMPARTS(2022, 01, 31) AS DateValue
DATETIMEFROMEPARTS is a sibling function that requires more data but outputs a fully-fledged datetime value. On top of the year, month and day, add an hour, minute, seconds and milliseconds. You won't be able to see the time part in the Salesforce Marketing Cloud UI (Contact Builder or Email Studio), but it will be available for calculations.
, DATETIMEFROMPARTS(2022, 01, 31, 23, 59, 59, 0) AS DatetimeValue
There is also a third function:
TIMEFROMEPARTS. It requires an hour, minute, seconds, fraction and precision as arguments. Its usefulness in Marketing Cloud is close to none.
Of course, you don't have to pass hardcoded integers as arguments for those functions - you can pass existing fields or even calculated values.
However, keep in mind that if at least one provided date part is a
NULL, the whole output will be a
DATEADD functions give you the power to add and subtract from your date values.
GETUTCDATE() AS TrialStartDate
, DATEADD(DAY, 7, GETUTCDATE()) AS TrialEndDate
It requires three things:
- Date part - The date element that will be changed
- Number - The integer (or an expression evaluating to integer) for interval
- Date - The original date that the
DATEADDfunction will change. It can be in any date data type. The output will be of the same type.
DATEADD does not take into consideration timezone offset.
However, if you change the date with the 31st day of the month by some months and the outcome month is shorter, SQL will account for that and return the last day of a given month.
DATEADD is especially useful when used with the
WHERE statement, as it allows to filter records dynamically:
WHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())
It's even better with ranges:
WHERE EventDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE())
A similar date calculation is also available with the
DATEADD() AMPScript function. Depending on the use case, consider whether you want to manipulate the date in the Data Extension or only in the communication.
DATEDIFF function calculates the difference between two dates.
You need to provide the date part used for the calculation and two dates (start and end order).
You may use it in the
, DATEDIFF(DAY, DateJoined, GETDATE()) AS SubscriptionTime
You may use it in the
WHERE DATEDIFF(DAY, DateJoined, GETDATE()) = 365
DATEDIFF can output positive and negative numbers depending on provided values and their order.
DATEDIFF(DAY, DATEADD(DAY, -1, GETDATE()), GETDATE()) AS YesterdayPostive /* Output: 1 */
, DATEDIFF(DAY, GETDATE(), DATEADD(DAY, -1, GETDATE())) AS YesterdayNegative /* Output: -1 */
It is crucial for creating correct comparisons within
It takes into consideration the leap years. If you provide dates with offset, it will include the timezone differences and Daylight Saving Time.
You can also use
DATEDIFF for capturing ranges.
WHERE DATEDIFF(DAY, GETDATE(), EventDate) <= 31
DATEDIFF(MONTH, 'Mar 31 2021', 'Apr 01 2021') AS ShortDiff /* Output: 1 */
, DATEDIFF(MONTH, 'Mar 01 2021', 'Apr 30 2021') AS LongDiff /* Output: 1 */
Despite dates in the first
DATEDIFF being just a day apart and in the second nearly two months apart, both return
1. The reason is that
DATEDIFF compares only the month date part - March to April is a single month of difference.
Depending on the use case, it might be good or bad, so be sure to select the correct function for your needs.
There is a limit to how big the difference can be. If you go down to the
SECOND date part, the query's maximum time difference is 68 years, 19 days, 3 hours, 14 minutes and 7 seconds. It shouldn't be an issue in Marketing Cloud for most use cases.
AT TIME ZONE
AT TIME ZONE function provides two incredible features:
- It can give timezone offset information for any datetime missing this data.
- It can convert datetime with offset to a different timezone.
It's a must-have for multi-country senders and beneficial for others planning to personalize or calculate using date data.
Add offset with AT TIME ZONE
When you work with timezones, you want to have offset for the datetime fields you are using. Unfortunately, in most cases, it is missing. Neither Salesforce Marketing Cloud system timestamps nor the popular
GETDATE function provides offset information.
You can quickly fix it with
AT TIME ZONE. When you apply this function to a timezone-less datetime, it will assume that you are defining it and return value with an appropriate offset.
CONVERT(DATETIME2(0), '2020-01-01T18:00:00') AT TIME ZONE 'Central Standard Time' AS CST /* Output: 2020-01-01 18:00:00 -06:00 */
, CONVERT(DATETIME2(0), '2020-07-01T18:00:00') AT TIME ZONE 'Central Standard Time' AS CDT /* Output: 2020-07-01 18:00:00 -05:00 */
In the above snippet, we
CONVERT the dates to datetime without offset. Then the
AT TIME ZONE function adds the offset and calculates the Daylight Saving Time offset change for you.
Not all available timezones observe Daylight Saving Time. For example,
Central Standard Time observes DST, while
Central America Standard Time does not. Always double-check the behaviour of the timezone you are selecting for your query.
AT TIME ZONE vs TODATETIMEOFFSET
As astonishing as it might seem, taking Daylight Saving Time into consideration is a problem when working with Salesforce Marketing Cloud system dates (including the
Salesforce Marketing Cloud uses Central Standard Time (UTC-6) without changes between standard and daylight savings time. It means that
AT TIME ZONE's excellent feature is working against us.
There are, however, two workarounds:
Central America Standard Time. It is a CST-like timezone that stays at the same offset for the whole year.
CONVERT(DATETIME2(0), '2020-01-01T18:00:00') AT TIME ZONE 'Central America Standard Time' AS CST /* Output: 2020-01-01 18:00:00 -06:00 */
, CONVERT(DATETIME2(0), '2020-07-01T18:00:00') AT TIME ZONE 'Central America Standard Time' AS AlsoCST /* Output: 2020-07-01 18:00:00 -06:00 */
TODATETIMEOFFSET. It is a simplified version of the
AT TIME ZONEthat adds arbitrary offset without any logic on Daylight Saving Time. Just as we would like for the SFMC system dates.
To use it, provide a datetime and an offset (for example,
TODATETIMEOFFSET(CONVERT(DATETIME2(0), '2020-01-01T18:00:00'), '-06:00') AS CST /* Output: 2020-01-01 18:00:00 -06:00 */
, TODATETIMEOFFSET(CONVERT(DATETIME2(0), '2020-07-01T18:00:00'), '-06:00') AS AlsoCST /* Output: 2020-07-01 18:00:00 -06:00 */
Of course, for any datetime values that include the Daylight Saving Time - pushed from a website or your data warehouse -
AT TIME ZONE will be the better solution.
Change timezone with AT TIME ZONE
AT TIME ZONE is useful also for changing the timezone of a datetime with offset.
You use it as previously - by providing the original datetime and the target timezone. But because the first argument already contains the offset, the function will convert the time to a new timezone in this scenario.
SELECT CONVERT(DATETIMEOFFSET(0), '2018-01-01 18:00:00 -06:00') AT TIME ZONE 'Central European Standard Time' AS CSTtoCET /* Output: 2020-01-02 01:00:00 +01:00 */
It will also automatically consider any differences between Daylight Saving Time start and end times for each timezone.
You can leverage both features of the
AT THE TIMEZONE in one line to apply the timezone to offset-less datetime and convert it to another timezone by using the function twice:
SELECT CONVERT(DATETIME2(0), '2020-01-01T18:00:00') AT TIME ZONE 'Central Standard Time' AT TIME ZONE 'Central European Standard Time' AS DateToCSTtoCET, /* Output: 2020-01-02 01:00:00 +01:00 */
Of course, for Salesforce Marketing Cloud system dates, we still need either of the workarounds:
GETDATE() AT TIME ZONE 'Central America Standard Time' AT TIME ZONE 'Central European Standard Time' AS SystemDateToCSTtoCET_v1
, TODATETIMEOFFSET(GETDATE(), '-06:00') AT TIME ZONE 'Central European Standard Time' AS SystemDateToCSTtoCET_v2
You can choose from 140 available timezones:
Dateline Standard Time,
Aleutian Standard Time,
Hawaiian Standard Time,
Marquesas Standard Time,
Alaskan Standard Time,
Pacific Standard Time (Mexico),
Pacific Standard Time,
US Mountain Standard Time,
Mountain Standard Time (Mexico),
Mountain Standard Time,
Central America Standard Time,
Central Standard Time,
Easter Island Standard Time,
Central Standard Time (Mexico),
Canada Central Standard Time,
SA Pacific Standard Time,
Eastern Standard Time (Mexico),
Eastern Standard Time,
Haiti Standard Time,
Cuba Standard Time,
US Eastern Standard Time,
Paraguay Standard Time,
Atlantic Standard Time,
Venezuela Standard Time,
Central Brazilian Standard Time,
SA Western Standard Time,
Pacific SA Standard Time,
Turks And Caicos Standard Time,
Newfoundland Standard Time,
Tocantins Standard Time,
E. South America Standard Time,
SA Eastern Standard Time,
Argentina Standard Time,
Greenland Standard Time,
Montevideo Standard Time,
Magallanes Standard Time,
Saint Pierre Standard Time,
Bahia Standard Time,
Mid-Atlantic Standard Time,
Azores Standard Time,
Cape Verde Standard Time,
Morocco Standard Time,
GMT Standard Time,
Greenwich Standard Time,
W. Europe Standard Time,
Central Europe Standard Time,
Romance Standard Time,
Central European Standard Time,
W. Central Africa Standard Time,
Namibia Standard Time,
Jordan Standard Time,
GTB Standard Time,
Middle East Standard Time,
Egypt Standard Time,
E. Europe Standard Time,
Syria Standard Time,
West Bank Standard Time,
South Africa Standard Time,
FLE Standard Time,
Israel Standard Time,
Kaliningrad Standard Time,
Libya Standard Time,
Arabic Standard Time,
Turkey Standard Time,
Arab Standard Time,
Belarus Standard Time,
Russian Standard Time,
E. Africa Standard Time,
Iran Standard Time,
Arabian Standard Time,
Astrakhan Standard Time,
Azerbaijan Standard Time,
Russia Time Zone 3,
Mauritius Standard Time,
Saratov Standard Time,
Georgian Standard Time,
Caucasus Standard Time,
Afghanistan Standard Time,
West Asia Standard Time,
Ekaterinburg Standard Time,
Pakistan Standard Time,
India Standard Time,
Sri Lanka Standard Time,
Nepal Standard Time,
Central Asia Standard Time,
Bangladesh Standard Time,
Omsk Standard Time,
Myanmar Standard Time,
SE Asia Standard Time,
Altai Standard Time,
W. Mongolia Standard Time,
North Asia Standard Time,
N. Central Asia Standard Time,
Tomsk Standard Time,
China Standard Time,
North Asia East Standard Time,
Singapore Standard Time,
W. Australia Standard Time,
Taipei Standard Time,
Ulaanbaatar Standard Time,
North Korea Standard Time,
Aus Central W. Standard Time,
Transbaikal Standard Time,
Tokyo Standard Time,
Korea Standard Time,
Yakutsk Standard Time,
Cen. Australia Standard Time,
AUS Central Standard Time,
E. Australia Standard Time,
AUS Eastern Standard Time,
West Pacific Standard Time,
Tasmania Standard Time,
Vladivostok Standard Time,
Lord Howe Standard Time,
Bougainville Standard Time,
Russia Time Zone 10,
Magadan Standard Time,
Norfolk Standard Time,
Sakhalin Standard Time,
Central Pacific Standard Time,
Russia Time Zone 11,
New Zealand Standard Time,
Fiji Standard Time,
Kamchatka Standard Time,
Chatham Islands Standard Time,
Tonga Standard Time,
Samoa Standard Time,
Line Islands Standard Time.
Don't worry about Daylight Saving Time names -
AT TIME ZONE use the Standard ones and will take care of DST.
FORMAT with Dates
FORMAT works only with datetime and numeric values, outputs a string and requires:
- Value to be formatted (can be a column, string or a function)
- Format specifier to be applied
- Optionally - ISO culture code
It is a perfect tool to manipulate any dates (or date-like strings with the help of conversion) available in your Data Extensions. For personalization. For comparison between system and imported dates outside of the query. For preparing data to export it to an external system with specific format requirements.
FORMAT(CONVERT(DATE, '10/30/2020'), 'd', 'en-GB') AS DateOrderedProperly
, FORMAT(GETUTCDATE(), 'dd/MM/yyyy') AS TodayCustomFormat
, FORMAT(DateJoined, 'D', 'de-DE') AS DateExpandedIndian
In the example above, the first two
FORMAT functions return the date in the same format -
30/10/2020 - one by using manually provided value, another by taking the current UTC date from the function.
The last line outputs
Freitag, 7. Februar 2020. Neat for running it with
CASE to change your short date into an extended date personalized on user language.
Standard date formats
Above, you have seen three different format specifiers used to manipulate the date, but many more are available. You can find the most useful below:
|Format specifier||Description||Example (for US culture)|
|'D'||Long date||Friday, October 30, 2020|
|'m'/'M'||Month Day||October 30|
|'y'/'Y'||Year Month||October 2020|
|'t'||Short time||2:15 PM|
|'T'||Long time||2:15:30 PM|
|'g'||General short time||10/30/2020 2:15 PM|
|'G'||General long time||10/30/2020 2:15:30 PM|
|'f'||Full short time||Friday, October 30, 2020 2:15 PM|
|'F'||Full long time||Friday, October 30, 2020 2:15:30 PM|
|'r'/'R'||RFC1123||Fri, 30 Oct 2020 14:15:30 GMT|
As highlighted in the table header - the examples are for the SFMC default US culture formating. If you add a specific culture code as a third parameter, the outcome will be different, as various countries have different defaults of elements order and separator style.
You can find the complete list of standard date format specifiers on Microsoft .Net Doc Pages.
Custom date formats
Of course, you are not limited to the formats shown above. Whenever you want something different, you can create a custom format by just providing a full pattern instead of a short specifier by using those elements:
|Format specifier||Description||Example (for US culture)|
|'d'||Day of the month||1|
|'dd'||Zero-based day of the month||01|
|'ddd'||Abbreviated day of the month||Mon|
|'dddd'||Full day of the month||Monday|
|'yy'||Last two digits of the year||20|
|'yyyy'||Four digits year||2020|
|'h'||Hour (12-hour clock)||1|
|'hh'||Zero-based hour (12-hour clock)||01|
|'H'||Hour (24-hour clock)||13|
|'HH'||Zero-based hour (24-hour clock)||01|
Again, the examples are for the default US culture formatting and might look different for different cultures (for example, the hour separator might be changed automatically to
. for Italy).
You can use the above format specifiers to obtain the exact format needed:
SELECT FORMAT(GETUTCDATE(), 'dddd (dd MMMM yyyy) - HH:mm', 'en-GB') AS FormattedDate
The above query will return
Saturday (30 October 2020) - 02:15.
You can find the complete list of custom date format specifiers on Microsoft .Net Doc Pages.
AMPScript uses very similar custom formatting with
FORMATDATE() functions. Depending on the use case, consider whether you want to manipulate the date visual representation in the Data Extension or only in the communication.