# Common Formats Fields used by PDI transformation steps and job entries support **format masks** (also called *conversion masks*) for converting data values from your input source format to your target output format. For example, you might have a date formatted as `MMddyyyy` in a database field and another date formatted as `ddMMyy` in a text file. You can apply a conversion mask to either field to match the other. ### Date formats PDI uses date and time patterns to parse and format date values. {% hint style="info" %} Date format symbols are case-sensitive. For example, `M` is month and `m` is minute. {% endhint %} The following table describes common date format symbols. | Symbol | Meaning | Type | Example | | ------ | ---------------------------- | -------------- | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | | G | Era | Text | `GG` → `AD` | | y | Year | Number |

yy → 03
yyyy → 2003

| | M | Month | Text or Number |

M → 7
M → 12
MM → 07
MMM → Jul
MMMM → December

| | d | Day in month | Number |

d → 3
dd → 03

| | h | Hour (1–12, AM/PM) | Number |

h → 3
hh → 03

| | H | Hour (0–23) | Number |

H → 15
HH → 15

| | k | Hour (1–24) | Number |

k → 3
kk → 03

| | K | Hour (0–11, AM/PM) | Number |

K → 3
KK → 03

| | m | Minute | Number |

m → 7
m → 15
mm → 15

| | s | Second | Number |

s → 15
ss → 15

| | S | Millisecond (0–999) | Number | `SSS` → `007` | | E | Day in week | Text |

EEE → Tue
EEEE → Tuesday

| | D | Day in year (1–365 or 1–366) | Number |

D → 65
DDD → 065

| | F | Day of week in month (1–5) | Number | `F` → `1` | | w | Week in year (1–53) | Number | `w` → `7` | | W | Week in month (1–5) | Number | `W` → `3` | | a | AM/PM | Text |

a → AM
aa → AM

| | z | Time zone | Text |

z → EST
zzz → EST
zzzz → Eastern Standard Time

| | X | Time zone offset | Text | `XXX` → `-08:00` | | ' | Escape for text | Delimiter | `hour'h'` → `hour 9` | | '' | Single quote | Literal | `ss''SSS` → `45'876` (use two quotes in a row to create a single quote) | ### Number formats PDI uses number patterns to parse and format numeric values. {% hint style="info" %} The actual decimal separator and grouping separator depend on your locale and, in many steps, the **Decimal symbol** and **Grouping symbol** options. {% endhint %} The following table describes common number format symbols. | Symbol | Location | Localized? | Meaning | | ------ | -------------------- | ---------- | ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ | | `0` | Number | Yes | Digit | | `#` | Number | Yes | Digit; zero shows as absent | | `.` | Number | Yes | Decimal separator or monetary decimal separator | | `-` | Number | Yes | Minus sign | | `,` | Number | Yes | Grouping separator | | `E` | Number | Yes | Separates mantissa and exponent in scientific notation. `E` does not need to be in quotation marks in a prefix or suffix. | | `;` | Sub-pattern boundary | Yes | Separates positive and negative sub-patterns | | `%` | Prefix or suffix | Yes | Multiply by 100 and show as percentage | | `‰` | Prefix or suffix | Yes | Multiply by 1000 and show as per mille | | `¤` | Prefix or suffix | No | Currency sign, replaced by the currency symbol. If doubled, replaced by the international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. | | `'` | Prefix or suffix | No | Quotes special characters in a prefix or suffix. For example, `'#'#` formats `123` as `#123`. To create a single quote, use two in a row: `# o''clock`. | ### Examples #### Date format examples The following examples use an input value of `2025-12-16 14:03:09.127`. * `yyyy-MM-dd` → `2025-12-16` * `MM/dd/yyyy` → `12/16/2025` * `yyyyMMdd` → `20251216` * `EEE, MMM d, yyyy` → `Tue, Dec 16, 2025` * `yyyy-MM-dd HH:mm:ss.SSS` → `2025-12-16 14:03:09.127` * `yyyy-MM-dd 'at' HH:mm` → `2025-12-16 at 14:03` {% hint style="info" %} To include literal text in a date pattern, wrap it in single quotes. To include a single quote, use two in a row. {% endhint %} #### Number format examples The following examples assume a grouping symbol of `,` and a decimal symbol of `.`. * Value `1234.5` * `0.00` → `1234.50` * `#,##0.00` → `1,234.50` * `¤#,##0.00` → `$1,234.50` (currency symbol depends on your locale) * Value `0.1261` * `0.00%` → `12.61%` * Value `-1234.5` * `#,##0.00;(#,##0.00)` → `(1,234.50)` ### See also Conversion masks (also called *formats*) are used by many PDI transformation steps and job entries to convert values between your input and output representations. For example, you might have: * A date field from a database formatted as `MMddyyyy` * A date field from a text file formatted as `ddMMyy` You can apply a conversion mask so both fields use the same format. ### Examples Use these examples as a starting point when you set **Format** or **Conversion mask** in a step. #### Date format examples * **ISO-like date**: `yyyy-MM-dd` → `2003-07-03` * **ISO-like date and time (milliseconds)**: `yyyy-MM-dd HH:mm:ss.SSS` → `2003-07-03 09:15:45.876` * **Compact date**: `yyyyMMdd` → `20030703` * **Month-day-year**: `MMddyyyy` → `07032003` * **Day-month-year (2-digit year)**: `ddMMyy` → `030703` * **Date with a literal character**: `yyyy-MM-dd 'at' HH:mm` → `2003-07-03 at 09:15` {% hint style="info" %} To include a single quote in the output, escape it by using two single quotes in a row. Example: `h 'o''clock' a` → `9 o'clock AM` {% endhint %} #### Number format examples * **Integer with grouping**: `#,##0` → `12,345` * **Fixed decimals**: `#,##0.00` → `12,345.60` * **Optional decimals**: `0.###` → `12`, `12.3`, or `12.345` * **Percentage**: `0.00%` → `12.34%` * **Currency (symbol depends on locale)**: `¤#,##0.00` → `$12,345.60` * **Negative sub-pattern (parentheses for negatives)**: `#,##0.00;(#,##0.00)` → `(12,345.60)` {% hint style="info" %} Number formatting can be affected by locale and by step settings such as **Decimal symbol**, **Grouping symbol**, and **Currency symbol**. {% endhint %} ### Date formats Use the following symbols to define common date formats. {% hint style="info" %} PDI date masks follow the same pattern symbols as Java date and time formatting. {% endhint %} | Symbol | Meaning | Type | Example | | ------ | ---------------------------- | -------------- | ------------------------------------------------------------------ | | `G` | Era | Text | `GG` → `AD` | | `y` | Year | Number | `yy` → `03`, `yyyy` → `2003` | | `M` | Month | Text or number | `M` → `7` or `12`, `MM` → `07`, `MMM` → `Jul`, `MMMM` → `December` | | `d` | Day in month | Number | `d` → `3`, `dd` → `03` | | `h` | Hour (1–12, AM/PM) | Number | `h` → `3`, `hh` → `03` | | `H` | Hour (0–23) | Number | `H` → `15`, `HH` → `15` | | `k` | Hour (1–24) | Number | `k` → `3`, `kk` → `03` | | `K` | Hour (0–11, AM/PM) | Number | `K` → `3`, `KK` → `03` | | `m` | Minute | Number | `m` → `7` or `15`, `mm` → `15` | | `s` | Second | Number | `s` → `15`, `ss` → `15` | | `S` | Millisecond (0–999) | Number | `SSS` → `007` | | `E` | Day in week | Text | `EEE` → `Tue`, `EEEE` → `Tuesday` | | `D` | Day in year (1–365 or 1–364) | Number | `D` → `65`, `DDD` → `065` | | `F` | Day of week in month (1–5) | Number | `F` → `1` | | `w` | Week in year (1–53) | Number | `w` → `7` | | `W` | Week in month (1–5) | Number | `W` → `3` | | `a` | AM/PM | Text | `a` → `AM` | | `z` | Time zone | Text | `z` → `EST`, `zzzz` → `Eastern Standard Time` | | `X` | Time zone offset | Text | `XXX` → `-08:00` | | `'` | Escape for text | Delimiter | `h 'o''clock' a` → `9 o'clock AM` | | `''` | Single quote | Literal | `ss''SSS` → `45'876` | ### Number formats Use the following symbols to define common number formats. * Many steps include a **Conversion mask** field (for example, [Calculator](https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/calculator)).