Break from the limits of source data types. Use the full power of SQL to transform your values.
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 conversion functions, that I find most useful for Marketing Automation purposes. They will help you change the value types to enable the use of type-based Salesforce Marketing Cloud User Interface elements and type-specific functions.
In Salesforce Marketing Cloud, you can use two universal conversion functions -
The first one,
CAST, is straightforward and available in all SQL dialects:
SELECT SubscriberKeyFROM Ent._SubscribersWHERE DateJoined > CAST('2020-10-30' AS DATE)
It takes a value and expected datatype with
AS operator in between.
However, in Salesforce Marketing Cloud, it is much better to use
CONVERT function instead. It covers all features of
CAST plus adds quite a lot more. It is also better supported by the Query Studio (for example, you cannot use
CAST in a
SELECT part of the query there).
The basic form of
CONVERT is nearly the same as the
CAST with reversed order of arguments:
SELECT SubscriberKeyFROM Ent._SubscribersWHERE DateJoined > CONVERT(DATE, '2020-10-30')
With both functions you can stack functions within for more complex calculations:
SELECT CONVERT(DATE, DATEADD(MM, 1, GETUTCDATE())) AS TrialPeriodEndDate
Up to this point,
CONVERT seem similar in the features.
CONVERT have an additional third optional argument - style. Thanks to it, you can convert even from non-standard (for the server) formats that would result in an error:
SELECT SubscriberKeyFROM _SubscribersWHERE DateJoined > CONVERT(DATE, '30/10/2019') /* Error */
With style codes, we can make it work with minimal change:
SELECT SubscriberKeyFROM _SubscribersWHERE DateJoined > CONVERT(DATE, '30/10/2019', 103)
It is not possible with
You Should Know
SELECT CONVERT(NVARCHAR, GETDATE(), 101) AS DateFormat1 /* Output: 10/30/2020 */ , CONVERT(NVARCHAR, GETDATE(), 102) AS DateFormat2 /* Output: 2020.10.30 */ , CONVERT(NVARCHAR, GETDATE(), 107) AS DateFormat3 /* Output: Oct 30, 2020 */
There are over 30 data types available for conversion, but there are only a few that are useful on a day-to-day basis in Salesforce Marketing Cloud:
|NCHAR||Fixed-length string with Unicode support|
|NVARCHAR||Variable-length string with Unicode support||Best option for most SFMC string use cases|
|DECIMAL/NUMERIC||Decimal number with big precision||Best option if you need a decimal number|
|INT||Integer number||Best option if your number is not decimal|
|BIGINT||Big integer number||When your number is bigger than 2,147,483,647, you need BIGINT|
|DATETIME||Legacy date and time||More friendly default formatting|
|DATETIME2||Modern date and time||Best when you need both date and time|
|DATETIMEOFFSET||Date with offset||When you need timezone offset|
|DATE||Only date||Best when you need date only|
|TIME||Only time||Best when you need time only|
You Should Know
There are also two more data types:
REAL. Don't use them. Both are Approximate Numeric Data Types and can lead to unpredicted behaviour, especially when used for equality-based conditions in
NUMERIC is the way to go.
When converting values to the selected data type, you can have even more control by passing optional argument right after type to declare the length of the output.
It is instrumental with
DECIMAL as it allows you to provide expected precision (number of digits in a number) and scale (number of digits to the right of the decimal point in a number).
For example, you may have a string field containing a product price -
'123.99'. If you convert it to
DECIMAL without any arguments, it will round to
124. However, you can provide precision and scale to keep the current format:
SELECT CONVERT(DECIMAL, '123.99') AS RoundedConversion /* Output: 124 */ , CONVERT(DECIMAL(5,2), '123.99') AS FullConversion /* Output: 123.99 */
Be sure to add correct precision. If it is smaller then the values in your source, it will lead to an error. It's better to have too big precision than too small.
As for scale - if your scale is smaller than in your source, the value is rounded.
You can also use the single argument with
DATETIMEOFFSET (to control precision of the output) and string data types (
NVARCHAR). The latter won't directly limit the number of characters, but rather the number of bytes (which might be equal or not to the number of characters depending on what characters you are using).
CHAR allows for 1-8000 bytes range, whereas
NCHAR due to Unicode support allows for only 1-4000.
NVARCHAR support the same ranges respectively and have an additional possible option -
max that you can use if some values might exceed the range.
You Should Know
Always double-check the choice of data type you want to convert to, as it might have a massive impact into outcome.
SELECT CAST(6.9 AS INT) AS CastToInt /* Output: 6 */ , CAST(6.9 AS DECIMAL) AS CastToNumeric /* Output: 7 */ , CONVERT(INT, 6.9) AS ConvertToInt /* Output: 6 */ , CONVERT(DECIMAL, 6.9) AS ConvertToNumeric /* Output: 7 */
It is happening because conversions from
INT are truncated. The rest is rounded if no specific precision/scale argument is available.
Just as with data types - there are many style codes available. Here I will cover just the most popular ones (if you don't understand some formats codes, check date formats guide):
|Date format||Style code|
|MMM dd yyyy hh:mmtt||100|
|dd MMM yyyy||106|
|MMM dd, yyyy||107|
|MM/dd/yy hh:mm:ss tt||22|
Use it by adding the style code as the third argument of the
SELECT CONVERT(NVARCHAR, GETDATE(), 101) AS DateAsString , CONVERT(DATETIME2, '2020-12-31 19:00:00', 120) AS StringAsDate
You can find the full list of style codes on Microsoft .Net Doc Pages.