Skip to main content

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 the 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.

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 AT TIME ZONE function.

You can also leverage `TODATETIMEOFFSET' to work with standard GETDATE.

The 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.

However, you can limit its output to either 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 the 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 the DATEADD function will change. It can be in any date data type. The output will be of the same type.
You Should Know

DATEADD does not take into consideration timezone offset.

However, if you are changing the date with the 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.

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    , EmailAddressFROM Ent._SubscribersWHERE DateJoined >= DATEADD(MONTH, -1, GETDATE())

It's even better with ranges:

Find all Subscribers registered to an event within the next month
SELECT      SubscriberKey    , EmailAddressFROM EventRegistrationWHERE EventDate BETWEEN GETDATE() AND DATEADD(MONTH, 1, GETDATE())
You Should Know

A 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.

DATEPART#

The DATEPART function returns the chosen part of the provided date.

Find all Subscribers that joined in 2020
SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE DATEPART(YEAR, DateJoined) = 2020

It outputs an integer, so be sure you are using a number when making comparisons.

You Should Know

There are also three shortcuts for the most popular use cases: YEAR(), MONTH() and DAY().

Find all Subscribers that joined in 2020 - shorter approach
SELECT      SubscriberKey    , EmailAddressFROM Ent._SubscribersWHERE YEAR(DateJoined) = 2020

In the backend, those functions are just wrappers around DATEPART, so you can use whatever you prefer.

Date parts#

You can reference date parts both by their 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.

DATEDIFF#

The 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 SELECT statement:

Check how long someone was a subscriber
SELECT      SubscriberKey    , DATEDIFF(DAY, DateJoined, GETDATE()) AS SubscriptionTimeFROM Ent._Subscribers

You may use it in the WHERE statement:

Get everyone that hit one year on the subscription list
SELECT SubscriberKeyFROM Ent._SubscribersWHERE DATEDIFF(DAY, DateJoined, GETDATE()) = 365
You Should Know

DATEDIFF can output both positive and negative numbers depending on provided values and their order.

SELECT      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 WHERE.

It takes into consideration the leap years. If you provide dates with offset, it will even include the timezone differences and Daylight Saving Time.

You can also use DATEDIFF for capturing ranges.

Find all Subscribers registered to an event within next 31 days
SELECT      SubscriberKey    , EmailAddressFROM EventRegistrationWHERE DATEDIFF(DAY, GETDATE(), EventDate) <= 31

It is shorter than DATEADD but with a different level of control. Because DATEDIFF operates on Date Parts, it will take into consideration only the chosen part. For example:

Different ranges, same outcome
SELECT     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.

It might be good or bad depending on the use case, so be sure to select the correct function for your needs.

You Should Know

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. For most use cases, this shouldn't be an issue in Marketing Cloud use cases.

DATENAME#

The DATENAME functions return the value of the specified datepart from a given date.

Capture the whole day's name
SELECT      SubscriberKey    , DATENAME(WEEKDAY, DateJoined) AS DayJoinedFROM Ent._Subscribers

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 datepart.

Create an automation that will capture weekend meetings for Friday reminder
SELECT SubscriberKeyFROM SalesMeetingsWHERE DATENAME(WEEKDAY, DayOfMeeting) IN ('Friday', 'Saturday', 'Sunday')    AND DATEDIFF(DAY, GETDATE(), DayOfMeeting) < 3

AT TIME ZONE#

The AT TIME ZONE function provides two incredible features:

  1. It can give timezone offset information for any datetime missing this data.
  2. 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.

You Should Know

AT TIME ZONE always outputs DATETIMEOFFSET data type. If you need it in another type/format, use CONVERT or FORMAT.

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.

Daylight Saving Time magic
SELECT      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.

You Should Know

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 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 GETDATE).

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:

  1. Central America Standard Time. It is a CST-like timezone that stays at the same offset for the whole year.
No Daylight Saving Time magic
SELECT      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 */
  1. TODATETIMEOFFSET. It is a simplified version of the AT TIME ZONE that 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, '-06:00' or '+01:00'):

No Daylight Saving Time magic
SELECT      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.

Change CST datetime with offset to CET
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 Should Know

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:

First, apply the timezone. Then change the timezone
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 */

It works because the function's first execution adds the offset with AT TIME ZONE. Second, uses the output with an offset applied and change the timezone.

Of course, for Salesforce Marketing Cloud system dates, we still need the either of the workarounds:

No Daylight Saving Time magic but still changing the timezone
SELECT      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

Available timezones#

You can choose from 140 available timezones: Dateline Standard Time, UTC-11, Aleutian Standard Time, Hawaiian Standard Time, Marquesas Standard Time, Alaskan Standard Time, UTC-09, Pacific Standard Time (Mexico), UTC-08, 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, UTC-02, Mid-Atlantic Standard Time, Azores Standard Time, Cape Verde Standard Time, UTC, 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, UTC+12, Fiji Standard Time, Kamchatka Standard Time, Chatham Islands Standard Time, UTC+13, 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#

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

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 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, 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.

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.