SFMC SQL Date Functions

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#

The GETDATE function is the most straightforward way to get current timestamp within your query.

SELECT
GETDATE() as CurrentDate
You Should Know

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.

The GETUTCDATE function works the same as GETDATE but returns datetime in 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.

You can, however, limit its output to either only date or time part by using CONVERT or FORMAT Functions.

DATEADD#

The DATEADD functions give you the power to add and subtract from your date values.

Add seven days to current UTC date
SELECT
GETUTCDATE() AS TrialStartDate,
DATEADD(DAY, 7, GETUTCDATE()) as TrialEndDate

It requires three things:

  1. Date part - The date element that will be changed
  2. Number - The integer (or an expression evaluating to integer) for interval
  3. Date - The original date that will be changed by the DATEADD function. It can be in any date data type. The output will be of the same type.

DATEADD is especially useful when used with the WHERE statement, as it allows to filter records dynamically:

Find all Subscribers that joined within the last month
SELECT
SubscriberKey,
EmailAddress
FROM ENT._Subscribers
WHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())
You Should Know

DATEADD does not take into consideration timezone offset.

However, if you are changing the date with 31st day of the month by months and the outcome month is shorter, SQL will account for that and return the last day of a given month.

Date parts#

You can reference date parts both by its full name, as well as an abbreviation.

Date partAbbreviations
YEARyy, yyyy
QUARTERqq, q
MONTHmm, m
DAYOFYEARdy, y
DAYdd, d
WEEKwk, ww
WEEKDAYdw, w
HOURhh
MINUTEmi, n
SECONDss, s
MILLISECONDms
MICROSECONDmcs
NANOSECONDns

DAYOFYEAR, DAY, and WEEKDAY are considered the same date part and return the equal value.

For 99% of SFMC needs, you will be just fine with YEAR, MONTH, DAY, HOUR and MINUTE. Using only those with the full name will make your queries readable and straightforward for everyone.

You Should Know

Very similar date calculation is also available with 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.

FORMAT with Dates#

The FORMAT works only with datetime and numeric values, outputs a string and requires:

  1. Value to be formatted (can be a column, string or a function)
  2. Format specifier to be applied
  3. 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.

Instead of creating complex AMPScript logic to adapt dates, format them
SELECT
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 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 there are much more available. You can find the most useful below:

Format specifierDescriptionExample (for US culture)
'd'Short date10/30/2020
'D'Long dateFriday, October 30, 2020
'm'/'M'Month DayOctober 30
'y'/'Y'Year MonthOctober 2020
't'Short time2:15 PM
'T'Long time2:15:30 PM
'g'General short time10/30/2020 2:15 PM
'G'General long time10/30/2020 2:15:30 PM
'f'Full short timeFriday, October 30, 2020 2:15 PM
'F'Full long timeFriday, October 30, 2020 2:15:30 PM
'r'/'R'RFC1123Fri, 30 Oct 2020 14:15:30 GMT

Just as highlighted in the table header - the examples are for the SFMC default US culture formating. If you add 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 full 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 custom format by just providing full pattern instead of short specifier by using those elements:

Format specifierDescriptionExample (for US culture)
'd'Day of the month1
'dd'Zero-based day of the month01
'ddd'Abbreviated day of the monthMon
'dddd'Full day of the monthMonday
'M'Month1
'MM'Zero-based month01
'MMM'Abbreviated monthJan
'MMMM'Full monthJanuary
'yy'Last two digits of the year20
'yyyy'Four digits year2020
'h'Hour (12-hour clock)1
'hh'Zero-based hour (12-hour clock)01
'tt'AM/PM designatorAM
'H'Hour (24-hour clock)13
'HH'Zero-based hour (24-hour clock)01
'm'Minute1
'mm'Zero-based minute01
's'Seconds1
'ss'Zero-based seconds01
':'Time separator':'
'/'Date separator'/'

Again, the examples are for the default US culture formatting and might look different for different cultures (for example, 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

Above query will return Saturday (30 October 2020) - 02:15.

You can find the full list of custom date format specifiers on Microsoft .Net Doc Pages.

You Should Know

AMPScript uses very similar custom formatting with FORMAT() and 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.

Last updated on by Mateusz Dąbrowski