# 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)). --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.pentaho.com/pdia-data-integration/pdi-transformation-steps-reference-overview/common-formats.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.