Skip to main content

Conversion functions — Marketing Cloud SQL reference

Type conversion in MC SQL — CAST, CONVERT, TRY_CAST, TRY_CONVERT — plus the rule for explicit casts on every join key, why TRY_* prevents whole-Activity failures, and the date-style codes worth memorizing.

Reference·Last updated 2026-05-07·Drafted by Lira · Edited by German Medina

Conversion functions turn a value of one type into another. In MC SQL the choices are CAST (standard SQL, portable), CONVERT (T-SQL with style codes for date formatting), and the TRY_* variants that return NULL on failure instead of throwing. Most conversion bugs are silent: the engine does coerce types implicitly when you don't ask, and it picks a direction that's sometimes correct and sometimes catastrophic.

Official syntax

-- CAST: standard, portable, no style codes
SELECT CAST(SubscriberKey AS NVARCHAR(255)) AS KeyAsString
FROM master_subscribers;

SELECT CAST('2026-05-07' AS DATE) AS ParsedDate;

-- CONVERT: T-SQL specific, supports style codes for date formatting
SELECT CONVERT(VARCHAR, GETDATE(), 120) AS Iso8601;        -- 2026-05-07 14:23:11
SELECT CONVERT(VARCHAR, GETDATE(), 23)  AS DateOnly;       -- 2026-05-07
SELECT CONVERT(VARCHAR, GETDATE(), 101) AS UsFormat;       -- 05/07/2026
SELECT CONVERT(DECIMAL(18,2), '1234.5678');                -- 1234.57

-- TRY_CAST / TRY_CONVERT: return NULL on conversion failure
-- (instead of erroring out and killing the Activity)
SELECT TRY_CAST(LegacyAge AS INT) AS AgeOrNull
FROM master_subscribers;
-- Where LegacyAge is sometimes '42', sometimes 'unknown', sometimes empty

-- COALESCE the failure to a sensible default
SELECT COALESCE(TRY_CAST(LegacyAge AS INT), 0) AS Age
FROM master_subscribers;

The supported set:

| Function | What it does | On failure | |---|---|---| | CAST(value AS type) | Explicit conversion | Throws an error, fails the Activity | | CONVERT(type, value) | Same as CAST, T-SQL syntax | Throws an error | | CONVERT(type, value, style) | Conversion with format style (date strings) | Throws an error | | TRY_CAST(value AS type) | Like CAST but returns NULL on failure | Returns NULL | | TRY_CONVERT(type, value, style) | Like CONVERT but returns NULL on failure | Returns NULL |

Useful date-style codes (full list in Salesforce Help):

| Style | Format | Example | |---|---|---| | 120 | ISO 8601 with time | 2026-05-07 14:23:11 | | 121 | ISO 8601 with milliseconds | 2026-05-07 14:23:11.000 | | 23 | ISO 8601 date only | 2026-05-07 | | 101 | US mm/dd/yyyy | 05/07/2026 | | 103 | UK dd/mm/yyyy | 07/05/2026 | | 112 | ISO basic yyyymmdd | 20260507 | | 0 | Default mon dd yyyy hh:miAM/PM | May 7 2026 2:23PM |

Reference:

What survives in production

TRY_CAST over CAST whenever the source is dirty

A single bad row in the source — 'unknown' in a column you tried to CAST to INT — will fail the entire Activity and roll back nothing (because there are no transactions, see gotchas — #1). Use TRY_CAST when the source is anything other than your own clean data.

-- AT RISK — a single malformed row anywhere in master_subscribers
-- kills the Activity at row N, leaving N-1 rows half-written
INSERT INTO de_stg_ages
SELECT
  SubscriberKey,
  CAST(LegacyAge AS INT) AS Age
FROM master_subscribers;

-- DURABLE — bad rows get NULL, the Activity completes,
-- you can investigate the NULLs after
INSERT INTO de_stg_ages
SELECT
  SubscriberKey,
  TRY_CAST(LegacyAge AS INT) AS Age
FROM master_subscribers;

-- DURABLE + DEFAULT — bad rows get a sentinel value
INSERT INTO de_stg_ages
SELECT
  SubscriberKey,
  COALESCE(TRY_CAST(LegacyAge AS INT), -1) AS Age   -- -1 marks "unparseable"
FROM master_subscribers;

The TRY_CAST version succeeds and gives you a row count of NULLs (or sentinels) you can inspect. The CAST version surfaces the bad row by failing the whole pipeline — useful in development, dangerous in scheduled production.

Always cast SubscriberKey to NVARCHAR(255) on both sides of a join

SubscriberKey is stored as a string but often joined against integer external IDs, leading to silent miscounts. The pattern is in the JOIN reference but worth repeating here as a conversion rule:

-- DEFENSIVE — explicit cast on both sides + trim
SELECT s.SubscriberKey, e.LoyaltyTier
FROM _Subscribers s
INNER JOIN ext_loyalty e
  ON LTRIM(RTRIM(CAST(s.SubscriberKey AS NVARCHAR(255))))
   = LTRIM(RTRIM(CAST(e.UserId AS NVARCHAR(255))));

See JOIN, String functions, and gotchas — #9.

Implicit conversion picks a direction — and it's not always the safe one

When you compare '42' = 42 (string vs int), the engine implicitly casts one side. In T-SQL the rule is generally "string converts to int" — which fails on the row where LegacyAge is 'unknown' and kills the Activity. Make the conversion explicit so you know which direction it's going.

-- AT RISK — implicit conversion, fails on rows where LegacyAge is non-numeric
WHERE LegacyAge = 18

-- EXPLICIT — TRY_CAST handles bad rows gracefully
WHERE TRY_CAST(LegacyAge AS INT) = 18

-- ALSO EXPLICIT — compare strings on both sides (no conversion needed)
WHERE LegacyAge = '18'

Pick one of the explicit versions based on what the column actually holds. Don't let the implicit conversion decide for you.

CONVERT style codes for date formatting — pick ISO 8601 (style 120 or 23)

When converting dates to strings (for export, logs, file naming), the style code matters. Most international formats are ambiguous (05/07/2026 is May 7 in US, July 5 in UK). ISO 8601 (2026-05-07) is unambiguous and sorts correctly as a string.

-- AVOID — locale-specific, sorts incorrectly as string
SELECT CONVERT(VARCHAR, OrderDate, 101) AS DateForExport;  -- 05/07/2026

-- PREFER — ISO 8601, unambiguous, sorts as string
SELECT CONVERT(VARCHAR, OrderDate, 23) AS DateForExport;   -- 2026-05-07

The cost is a four-character difference in style code; the saving is a downstream system that sorts OrderDate strings correctly without converting back to dates.

Quick decision

Use TRY_CAST / TRY_CONVERT when:

  • The source data was imported from external systems (CSV, FTP drop, CRM export).
  • A failing row would kill a scheduled production Activity.
  • You'd rather see NULLs you can inspect than a Failed Activity step.

Use CAST (without TRY_) when:

  • The source is your own staged DE that has already been validated.
  • A failing cast actually means a bug you want to surface immediately (developer environment).

Use CONVERT instead of CAST when:

  • You need style codes for date string formatting.
  • Otherwise prefer CAST for portability.

Always wrap join keys in LTRIM(RTRIM(CAST(... AS NVARCHAR(255)))) when:

  • Joining SubscriberKey or any external ID across DEs.

Related

  • Basics — supported T-SQL subset
  • SELECT — CAST in projection
  • WHERE — implicit conversion in filter comparisons
  • JOIN — CAST on both sides of join keys
  • String functions — LTRIM(RTRIM()) companion to CAST
  • Numeric functions — CAST to DECIMAL for division precision
  • MC SQL gotchas — see #1 (no transactions makes failed casts catastrophic), #9 (SubscriberKey casting)

More function reference pages incoming: Aggregate · Null Functions.

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