Numeric functions — Marketing Cloud SQL reference | Cleon

Numeric functions in MC SQL are the standard T-SQL set. The bugs are mostly about three things: integer division truncates (5 / 2 returns 2, not 2.5), ROUND with negative scale rounds to the nearest 10 / 100 / 1000 (useful trick, easy to misread), and DECIMAL precision when the column stores money or loyalty points and the math has to be exact.

Official syntax

-- Absolute value
SELECT ABS(LoyaltyDelta) AS DeltaAbs FROM master_subscribers;

-- Rounding
SELECT
  CEILING(4.2)            AS Up,            -- 5
  FLOOR(4.8)              AS Down,          -- 4
  ROUND(4.567, 2)         AS TwoDec,        -- 4.57
  ROUND(4.567, 0)         AS WholeNumber,   -- 5
  ROUND(1234.567, -2)     AS NearestHundred -- 1200
FROM master_subscribers;

-- Power, square root, sign
SELECT
  POWER(2, 10)            AS TwoPowTen,    -- 1024
  SQRT(144)               AS RootOfNine,   -- 12
  SIGN(-7)                AS Negative,     -- -1
  SIGN(0)                 AS Zero,         -- 0
  SIGN(42)                AS Positive      -- 1
FROM master_subscribers;

-- Random number between 0 and 1 (non-deterministic per call)
SELECT RAND() AS RandomValue FROM master_subscribers;

-- Modulo via %
SELECT 17 % 5 AS Remainder;  -- 2

The supported set across most SFMC tenants:

| Function | What it does | Returns | |---|---|---| | ABS(n) | Absolute value | Same numeric type as input | | CEILING(n) | Smallest integer ≥ n | INT | | FLOOR(n) | Largest integer ≤ n | INT | | ROUND(n, scale) | Round n to scale decimal places (negative scale rounds left of decimal) | Same numeric type as n | | POWER(base, exp) | base raised to exp | FLOAT | | SQRT(n) | Square root | FLOAT | | SIGN(n) | -1, 0, or 1 based on the sign of n | INT | | RAND() | Pseudo-random float in [0, 1), non-deterministic | FLOAT | | n % m | Modulo (remainder of integer division) | INT | | + - * / | Standard arithmetic operators | Depends on operand types |

Reference:

Salesforce Help — Supported T-SQL syntax (functions section) ↗

What survives in production

Integer division truncates — the silent off-by-one source

5 / 2 in T-SQL returns 2, not 2.5. The result type is determined by the operand types, not the math. If both sides are INT, the division returns INT and the fractional part is dropped before you see it.

-- BUG — integer division, returns 0 when LoyaltyPoints < TotalSpend
SELECT
  SubscriberKey,
  LoyaltyPoints / TotalSpend AS PointsPerDollar
FROM master_subscribers;

-- CORRECT — cast one operand to a decimal type to force decimal division
SELECT
  SubscriberKey,
  CAST(LoyaltyPoints AS DECIMAL(18,4)) / TotalSpend AS PointsPerDollar
FROM master_subscribers;

-- ALSO CORRECT — multiply by 1.0 to coerce one operand
SELECT
  SubscriberKey,
  (LoyaltyPoints * 1.0) / TotalSpend AS PointsPerDollar
FROM master_subscribers;

The bug version compiles, runs, and returns truncated results — usually 0 — for every row where the numerator is smaller than the denominator. Easy to ship if no one runs the math by hand on a few rows.

`ROUND` with negative scale rounds left of the decimal point

This is a useful trick but reads as a typo to anyone who hasn't seen it. ROUND(1234.567, -2) returns 1200 because -2 means "round to the nearest hundred". ROUND(1234.567, -3) returns 1000.

-- Bucketing into hundreds for rough segmentation
SELECT
  SubscriberKey,
  ROUND(TotalSpend, -2) AS SpendBucket
FROM master_subscribers;
-- Spend 1234 → bucket 1200
-- Spend 1289 → bucket 1300

Document the negative-scale call with a comment when you use it. The next reader is going to assume it's a typo otherwise.

`DECIMAL` for money, never `FLOAT`

FLOAT is binary floating point. Adding 0.1 + 0.2 returns 0.30000000000000004 — invisible at small scale, devastating in financial sums. For loyalty points, currency, or any value that needs exact math, the destination DE column should be DECIMAL(precision, scale).

-- AT RISK — both columns FLOAT, sum drifts over millions of rows
INSERT INTO de_loyalty_summary
SELECT
  SubscriberKey,
  SUM(PurchaseAmount) AS TotalSpend
FROM purchases
GROUP BY SubscriberKey;
-- (where PurchaseAmount and TotalSpend are FLOAT)

-- DURABLE — DECIMAL columns at source AND destination, exact math
-- Destination DE column: TotalSpend DECIMAL(18, 2)
INSERT INTO de_loyalty_summary
SELECT
  SubscriberKey,
  CAST(SUM(CAST(PurchaseAmount AS DECIMAL(18,2))) AS DECIMAL(18,2)) AS TotalSpend
FROM purchases
GROUP BY SubscriberKey;

When a destination DE will hold money values, set the column type to DECIMAL(p, s) (commonly DECIMAL(18, 2) for currency to two decimals) and cast inputs to match. The cost is one CAST per column; the saving is reconciliation reports that match accounting.

WATCH OUT

RAND() is non-deterministic — each call returns a different value, even within the same query. If you reference RAND() more than once expecting the same value, every reference resolves separately:

-- AT RISK — three RAND() calls, three different values per row
SELECT
  SubscriberKey,
  RAND() AS r1,
  RAND() AS r2,                                -- different from r1
  CASE WHEN RAND() < 0.1 THEN 'sample' END AS InSample  -- different from r1 + r2
FROM master_subscribers;

For random sampling that has to be consistent, capture a single RAND() per row in a staging step:

-- Stage with one RAND per row
INSERT INTO de_stg_random_subs
SELECT SubscriberKey, RAND() AS RandValue
FROM master_subscribers;

-- Then read consistently
SELECT SubscriberKey
FROM de_stg_random_subs
WHERE RandValue < 0.1;   -- exactly 10% sample

Wrapping a numeric column in a function in `WHERE` kills the index

Same rule as string and date functions: wrapping the column itself prevents index use. Apply the math to the literal side instead.

-- AT RISK — function on the column, full scan
WHERE ABS(LoyaltyDelta) > 50

-- BETTER — algebraic equivalent, bare column on one side
WHERE LoyaltyDelta > 50 OR LoyaltyDelta < -50

The first version is clearer; the second runs faster on a large DE. Trade off based on the source size.

Quick decision

Cast to DECIMAL before division when:

Both operands are integers and you need a decimal result.
The math feeds money/points/percentages.

Use ROUND(n, -k) when:

You're bucketing values for segmentation. Document with a comment.

Use DECIMAL(p, s) columns instead of FLOAT when:

The destination stores money, loyalty points, percentages, or any value where rounding errors matter.

Stage RAND() once per row when:

The same random value needs to be referenced more than once.

Keep numeric columns bare in WHERE when:

The Data Extension has more than ~100k rows. Move the math to the literal side instead.

Basics — supported T-SQL subset
SELECT — numeric functions in projection
WHERE — numeric functions in filters (and the bare-column rule)
INSERT INTO — destination column types (DECIMAL vs FLOAT for money)
Aggregate functions — SUM, AVG, COUNT over numeric columns
Conversion functions — CAST for type coercion

More function reference pages incoming: Conversion · Aggregate · Null Functions.

Plus how-to snippets for common production debugging — email sends, value length, contact reach, etc.

Official syntax

What survives in production

Integer division truncates — the silent off-by-one source

ROUND with negative scale rounds left of the decimal point

DECIMAL for money, never FLOAT

Wrapping a numeric column in a function in WHERE kills the index

Quick decision

Related

`ROUND` with negative scale rounds left of the decimal point

`DECIMAL` for money, never `FLOAT`

Wrapping a numeric column in a function in `WHERE` kills the index