How to compare booleans, numbers and dates using AMPscript in SFMC

This article explains how to compare booleans, numbers and dates using AMPscript in Salesforce Marketing Cloud (SFMC).

The problem

Have you ever noticed anything strange happen in your code? Usually it works like a charm but sometimes, just sometimes, your masterfully written conditions return different results?

As a superstitious person you may think it’s the ghost of Marketing Cloud haunting you again, wearing Einstein’s wig and laughing loudly… Or, if you are paranoid enough, you may think it’s you arch-enemy of a colleague who’s messing with your code.

In this case, good news! It’s probably just you writing your conditions wrong and this article will teach you how to avoid losing your mind over simple data comparisons.

Data types and format

When it comes to data comparison, there are 2 main things to keep in mind: data type and format.

There are multiple data types in Marketing Cloud, but for the purpose of this article we will focus on the main ones: text, number, boolean and date.

What are they exactly?

TEXT: A chain of characters that can contain letters, numbers, symbols and spaces.
NUMBER: A single character ranging from 0 to 9.
BOOLEAN: A special value, equal to either TRUE or FALSE.
DATE: An expression of time, that can be formatted in numerous ways, according to the language and region.

Formatting is basically transforming the data in the way that it can be easily understood by the user or the system that processes the data.

Here’s an example of the same date, written in 3 different formats:

05/02/2023 10:15 PM (American format)
02/05/2023 22:15 (European format)
2023-05-02T22:15:00Z (System format, called ISO)

Don’t compare apples to oranges

Now that we know what the data types and the format are, let me ask you this: have you ever heard the expression – “Don’t compare apples to oranges“?

I want you to remember this every time you are performing a comparison in AMPscript.

It means that in order to perform a proper comparison that doesn’t fail, we need to have both values in the same data type and preferably in the same format.

But that’s not what happens in real life… More often than not, AMPscript functions return the data as text instead of the correct data type, or maybe the piece of data is provided in a format that Marketing Cloud can’t understand.

Let’s examine several examples in different data types and see how it works!

Compare Numbers

Comparing 2 numbers is a piece of cake, everything works as it should.

%%[
    SET @NUMBER_1 = 45
    SET @NUMBER_2 = 350

    IF @NUMBER_1 > @NUMBER_2 THEN
        SET @RESULT = "45 > 350"
    ELSE
        SET @RESULT = "350 > 45"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 350 > 45

But what happens if both numbers are pieces of text?

%%[
    SET @NUMBER_1 = "45"
    SET @NUMBER_2 = "350"

    IF @NUMBER_1 > @NUMBER_2 THEN
        SET @RESULT = "45 > 350"
    ELSE
        SET @RESULT = "350 > 45"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

WRONG RESULT: 45 > 350

As a matter of fact, the comparison of text operates one character at a time, from A to Z and from 0 to 9.

This means that when comparing “45” to “350“, we first compare “4” to “3” and since “4” is located further down the chain of characters “123456789“, the comparison stops there and as a result “45” is considered greater than “350”.

How do we solve this? How do we make pieces of text behave like numbers?

Unfortunately for us, AMPscript doesn’t have a dedicated function for transforming texts into numbers…

But it turns out that using a math function does the trick! All we have to do is to add zero to our piece of text using Add() function.

%%[
    SET @NUMBER_1 = "45"
    SET @NUMBER_2 = "350"

    SET @NUM_1 = Add(@NUMBER_1, 0)
    SET @NUM_2 = Add(@NUMBER_2, 0)

    IF @NUM_1 > @NUM_2 THEN
        SET @RESULT = "45 > 350"
    ELSE
        SET @RESULT = "350 > 45"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 350 > 45

Compare Booleans

As strange as it may seem, AMPscript doesn’t have any issues comparing 2 Boolean values.

%%[
	SET @BOOL_1 = "False"
	SET @BOOL_2 = false

    IF @BOOL_1 == @BOOL_2 THEN
        SET @RESULT = "False == false"
    ELSE
        SET @RESULT = "False <> false"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: False == false

Even when comparing 2 pieces of text written in different case, the result remains consistent and accurate.

%%[
	SET @BOOL_1 = "falSE"
	SET @BOOL_2 = "FALSE"

    IF @BOOL_1 == @BOOL_2 THEN
        SET @RESULT = "falSE == FALSE"
    ELSE
        SET @RESULT = "falSE <> FALSE"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: falSE == FALSE

Compare dates and time

Again, comparing dates as pieces of text is not recommended, as it can lead to wrong results!

%%[
    SET @DATE_1 = "2020-08-22 7:45 AM"
    SET @DATE_2 = "2020-08-22 1:30 PM"

    IF @DATE_1 > @DATE_2 THEN
        SET @RESULT = "2020-08-22 7:45 AM > 2020-08-22 1:30 PM"
    ELSE
        SET @RESULT = "2020-08-22 1:30 PM > 2020-08-22 7:45 AM"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

WRONG RESULT: 2020-08-22 7:45 AM > 2020-08-22 1:30 PM

Therefore, we first need to convert the text to a date value before making a proper comparison.

Standard dates

When it comes so standard dates (meaning the date formats officially supported by Marketing Cloud) we can easily make the conversion using the DateParse() function.

%%[
    SET @DATE_1 = DateParse("2020-08-22 7:45 AM")
    SET @DATE_2 = DateParse("2020-08-22 1:30 PM")

    IF @DATE_1 > @DATE_2 THEN
        SET @RESULT = "2020-08-22 7:45 AM > 2020-08-22 1:30 PM"
    ELSE
        SET @RESULT = "2020-08-22 1:30 PM > 2020-08-22 7:45 AM"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 2020-08-22 1:30 PM > 2020-08-22 7:45 AM

And since we are working with date values, we are also able to make the distinction using the DateDiff() function.

%%[
    SET @DATE_1 = DateParse("2020-08-22 7:45 AM")
    SET @DATE_2 = DateParse("2020-08-22 1:30 PM")

    IF DateDiff(@DATE_2, @DATE_1, "MI") > 1 THEN
        SET @RESULT = "2020-08-22 7:45 AM > 2020-08-22 1:30 PM"
    ELSE
        SET @RESULT = "2020-08-22 1:30 PM > 2020-08-22 7:45 AM"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 2020-08-22 1:30 PM > 2020-08-22 7:45 AM

Alternatively, we can also convert the dates in such a format that even a text comparison would return the correct result!

%%[
    SET @DATE_1 = "2020-08-22 7:45 AM"
    SET @DATE_2 = "2020-08-22 1:30 PM"

    SET @DATE_1_NUM = FormatDate(@DATE_1, "yyyyMMddHHmmss")
	SET @DATE_2_NUM = FormatDate(@DATE_2, "yyyyMMddHHmmss")

    IF @DATE_1_NUM > @DATE_1_NUM THEN
        SET @RESULT = "2020-08-22 7:45 AM > 2020-08-22 1:30 PM"
    ELSE
        SET @RESULT = "2020-08-22 1:30 PM > 2020-08-22 7:45 AM"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 2020-08-22 1:30 PM > 2020-08-22 7:45 AM

Standard time

Same method applies when comparing time.

%%[
    SET @TIME_1 = DateParse("7:45 AM")
    SET @TIME_2 = DateParse("7:45 PM")

    IF @TIME_1 > @TIME_2 THEN
        SET @RESULT = "7:45 AM > 7:45 PM"
    ELSE
        SET @RESULT = "7:45 PM > 7:45 AM"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 7:45 PM > 7:45 AM

But alternatively, we can also convert the time value into a number that can be used for a proper comparison.

%%[
    SET @TIME_1 = "7:45"
    SET @TIME_2 = "19:30"

    SET @TIME_1_NUM = Add(Replace(@TIME_1, ":", ""), 0)
    SET @TIME_2_NUM = Add(Replace(@TIME_2, ":", ""), 0)

    IF @TIME_1_NUM > @TIME_2_NUM THEN
        SET @RESULT = "7:45 > 19:30"
    ELSE
        SET @RESULT = "19:30 > 7:45"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 19:30 > 7:45

Non-standard dates

When it comes to non-standard dates, the trick is to transform it to a standard date first and then parse it.

String methods

Transforming the data can be done by using string methods, such as Substring(), Concat(), IndexOf() and Length().

%%[
    SET @DATE_1 = "22.08.2020 7:45"
    SET @DATE_2 = "22.08.2020 19:30"

    SET @DATE_1_DAY = Substring(@DATE_1, 0, 2)
    SET @DATE_1_MONTH = Substring(@DATE_1, 4, 2)
    SET @DATE_1_YEAR = Substring(@DATE_1, 7, 4)
    SET @DATE_1_TIME = Substring(@DATE_1, IndexOf(Trim(@DATE_1)," "), Length(@DATE_1))
    SET @DATE_1_STANDARD = DateParse(Concat(@DATE_1_MONTH, "-", @DATE_1_DAY, "-", @DATE_1_YEAR, " ", @DATE_1_TIME))

    SET @DATE_2_DAY = Substring(@DATE_2, 0, 2)
    SET @DATE_2_MONTH = Substring(@DATE_2, 4, 2)
    SET @DATE_2_YEAR = Substring(@DATE_2, 7, 4)
    SET @DATE_2_TIME = Substring(@DATE_2, IndexOf(@DATE_2," "), Length(@DATE_2))
    SET @DATE_2_STANDARD = DateParse(Concat(@DATE_2_MONTH, "-", @DATE_2_DAY, "-", @DATE_2_YEAR, " ", @DATE_2_TIME))

    IF @DATE_1_STANDARD > @DATE_2_STANDARD THEN
        SET @RESULT = "22.08.2020 7:45 > 22.08.2020 19:30"
    ELSE
        SET @RESULT = "22.08.2020 19:30 > 22.08.2020 7:45"
    ENDIF
]%%
	
%%=v(@RESULT)=%%

RESULT: 19:30 > 7:45

Regex method

Or, using Regular Expression patterns, which work just as well.

%%[
	SET @DATE_1 = "2/8/2020 7:45"
    SET @DATE_2 = "2/12/2020 19:30"

    SET @PATTERN = "^(\d{1,2})\/(\d{1,2})\/(\d{4})\s(.*)$"

    SET @DATE_1_DAY = RegExMatch(@DATE_1, @PATTERN, 1)
	SET @DATE_1_MONTH = RegExMatch(@DATE_1, @PATTERN, 2)
	SET @DATE_1_YEAR = RegExMatch(@DATE_1, @PATTERN, 3)
	SET @DATE_1_TIME = RegExMatch(@DATE_1, @PATTERN, 4)
	SET @DATE_1_STANDARD = DateParse(Concat(@DATE_1_MONTH, "-", @DATE_1_DAY, "-", @DATE_1_YEAR, " ", @DATE_1_TIME))
        
	SET @DATE_2_DAY = RegExMatch(@DATE_2, @PATTERN, 1)
	SET @DATE_2_MONTH = RegExMatch(@DATE_2, @PATTERN, 2)
	SET @DATE_2_YEAR = RegExMatch(@DATE_2, @PATTERN, 3)
	SET @DATE_2_TIME = RegExMatch(@DATE_2, @PATTERN, 4)
	SET @DATE_2_STANDARD = DateParse(Concat(@DATE_2_MONTH, "-", @DATE_2_DAY, "-", @DATE_2_YEAR, " ", @DATE_2_TIME))

	SET @RESULT = IIF(@DATE_1_STANDARD > @DATE_2_STANDARD, "DATE_1 > DATE_2", "DATE_1 < DATE_2")
]%%
%%=v(@RESULT)=%%

RESULT: 2/12/2020 19:30 > 2/8/2020 7:45

Conclusion

Mastering comparison operations in AMPscript is an absolute must!

Please make sure to remember that when it comes to comparisons, data types and format are key.

And as previously stated: – “Don’t compare apples to oranges“.

Have I missed anything?

Please poke me with a sharp comment below or use the contact form.