4.2 Formula Tool: Date/Time Functions & Type Casting

4.2 Formula Tool: Date/Time Functions & Type Casting

Core Concept: Alteryx Designer enforces strict ISO-8601 formatting for native Date and DateTime fields. Transforming strings into valid dates, calculating intervals between timestamps, formatting dates for business reporting, and programmatically converting between numbers and strings are high-yield core exam competencies performed using specialized Date/Time and Conversion functions in the Formula tool.

---

1. The Alteryx Date & DateTime ISO Standard

Alteryx stores native date and time values using standardized ISO string lengths and formats:

Date:     YYYY-MM-DD           (10 characters: e.g., '2026-08-28')
Time:     HH:MM:SS             (8 characters:  e.g., '15:30:00' or with subseconds '15:30:00.123')
DateTime: YYYY-MM-DD HH:MM:SS  (19 characters: e.g., '2026-08-28 15:30:00')

Any data stored in a Date or DateTime column must strictly conform to these representations. If a field contains dates in formats such as "08/28/2026" or "28-Aug-2026", it is stored as a String or V_WString until converted using DateTimeParse() or the DateTime Tool.

---

2. Core DateTime Formula Functions

The Formula tool provides a comprehensive suite of functions for generating timestamps, calculating date intervals, and adding durations.

Current System Date and Time

• DateTimeToday(): Returns the current system date as an ISO Date string ("YYYY-MM-DD"). Takes no arguments.
• DateTimeNow(): Returns the current system timestamp as an ISO DateTime string ("YYYY-MM-DD HH:MM:SS"). Takes no arguments.

DateTimeAdd: Adding & Subtracting Intervals

Calculates a future or past date by adding or subtracting an integer duration in specified units.

$\text{DateTimeAdd}(\text{dt}, \text{val}, \text{uom})$

• dt: A valid ISO Date or DateTime field or literal string.
• val: An integer representing the number of units to add (use positive numbers to add, negative numbers to subtract).
• uom: Unit of measure string (e.g., "days", "months", "years", "hours", "minutes", "seconds", "weeks").

// Add 30 days to an order date
DateTimeAdd([Order_Date], 30, "days")

// Subtract 6 months from a reference date
DateTimeAdd([Policy_Renewal], -6, "months")

// Add 4 hours to a timestamp
DateTimeAdd([Log_Time], 4, "hours")

DateTimeDiff: Calculating Elapsed Time

Calculates the difference between two dates ($dt_1 - dt_2$) expressed as an integer in the requested unit of measure.

$\text{DateTimeDiff}(dt_1, dt_2, \text{uom})$

• Rule: Computes $dt_1 - dt_2$. If $dt_1$ is after $dt_2$, the result is positive. If $dt_1$ is before $dt_2$, the result is negative.
• Integer Truncation: DateTimeDiff truncates to whole units (does not round up).

// Calculate customer age in full years
DateTimeDiff(DateTimeToday(), [BirthDate], "years")

// Calculate days between shipment and delivery
DateTimeDiff([Delivery_Date], [Ship_Date], "days")

// Calculate overdue days (positive if overdue, negative if remaining)
DateTimeDiff(DateTimeToday(), [Due_Date], "days")

DateTime Interval Units of Measure (uom)

Units of measure are passed as case-insensitive strings: "years", "months", "weeks", "days", "hours", "minutes", "seconds".

---

3. Date Formatting & Parsing (DateTimeFormat vs DateTimeParse)

Converting between human-readable date formats and Alteryx ISO standards is a frequent exam requirement.

                    ┌──────────────────────────────────────────────┐
                    │       Human / Custom Formatted String        │
                    │             "August 28, 2026"                │
                    └───────▲──────────────────────────────┬───────┘
                            │                              │
            DateTimeFormat()│                              │DateTimeParse()
     (Converts Date to Text)│                              │(Converts Text to Date)
                            │                              ▼
                    ┌───────┴──────────────────────────────────────┐
                    │         Alteryx ISO Standard Date            │
                    │                "2026-08-28"                  │
                    └──────────────────────────────────────────────┘

DateTimeFormat(dt, format)

Converts a standard ISO Date or DateTime field into a customized string according to format specifiers.

• Example: DateTimeFormat([OrderDate], "%B %d, %Y") transforms "2026-08-28" into "August 28, 2026".
• Target Field Type: Must be a String, V_String, or V_WString (since custom formats cannot be stored in native Date types).

DateTimeParse(string, format)

Converts a non-ISO date string into a standard Alteryx ISO Date or DateTime (YYYY-MM-DD).

• Example: DateTimeParse([Date_String], "%m/%d/%Y") transforms "08/28/2026" into "2026-08-28".
• Target Field Type: Date or DateTime.

Comprehensive Format Specifiers Catalog

Exam Trap: Case sensitivity matters in specifiers! %Y is a 4-digit year (2026), while %y is a 2-digit year (26). %m is month (01–12), while %M is minute (00–59). %B is full month (August), while %b is abbreviated month (Aug).

---

4. Explicit Type Casting: ToNumber() and ToString()

While the Select tool alters schema types globally, the Formula tool allows programmatic type conversion and string formatting within expressions.

ToNumber() Function

Converts a string representation of a number into a numeric value (Double or integer).

$\text{ToNumber}(x, [\text{bIgnoreErrors}], [\text{keepPercents}], [\text{decimalSymbol}])$

1. x: The string field to convert.
2. bIgnoreErrors (Optional, default 0):
   • 0: Generates a conversion error warning in the Results window if non-numeric characters are present, converting invalid values to [Null].
   • 1: Suppresses conversion warnings and converts invalid values to 0 or [Null] quietly.
3. keepPercents (Optional, default 0):
   • 0: Ignores % or treats it as non-numeric.
   • 1: Automatically divides percentage strings by 100 (e.g., "85%" converts to 0.85).
4. decimalSymbol (Optional, default "."):
   • Set to "," to parse European decimal notation (e.g., "1234,56" → 1234.56).

// Parse string percentage into decimal without warnings
ToNumber([Discount_Text], 1, 1, ".")

// Convert standard numeric string to number
ToNumber([Unit_Cost])

ToString() Function

Converts a numeric value, date, or boolean into a formatted text string.

$\text{ToString}(x, [\text{numDec}], [\text{addThousandsSeparator}], [\text{decimalSymbol}])$

1. x: The numeric value or field to convert.
2. numDec (Optional, default 0): Number of decimal places to format (rounds to specified scale).
3. addThousandsSeparator (Optional, default 0):
   • 0: No commas (e.g., "1234567.89").
   • 1 or true: Adds commas (e.g., "1,234,567.89").
4. decimalSymbol (Optional, default "."): Sets character for decimal point.

// Format revenue as currency string with commas and 2 decimals
"$" + ToString([Revenue], 2, 1)
// Output: "$1,450,200.50"

---

5. Conversion Errors, Nulls & Error Handling

When temporal or conversion functions encounter malformed data, Designer responds with consistent, deterministic rules:

• Invalid Date Parsing: DateTimeParse("InvalidDate", "%m/%d/%Y") returns [Null] and logs a Conversion Error in the Results window.
• Null Propagation: DateTimeAdd(Null(), 10, "days") evaluates to [Null].
• Out-of-Bounds Calendar Dates: DateTimeParse("02/30/2026", "%m/%d/%Y") returns [Null] because February 30th does not exist.