Common Formats
Fields used by PDI transformation steps and job entries have common masks for converting the formats of data values from your original input source or to your target output. For example, you may have a date field in a record of a database that is formatted to be MMddyyyy and another field in an input text file formatted to ddMMyy. You can apply a mask to either field to match the other.
Date Formats
The following table describes common date formats used by PDI transformation steps and job entries:
| 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" -> "15" "KK" -> "15" |
| 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-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" "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 quote marks in a row to create a single quote in a string. |
Number Formats
The following table describes common number formats used by PDI transformation steps and job entries:
| 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 quotes within a prefix or a suffix. |
| ; | Sub-pattern boundary | Yes |
Separated positive and negative sub-patterns |
| % | Prefix or suffix | Yes |
Multiply by 100 and show as percentage |
| ‰ (\u2030) | Prefix or suffix | Yes |
Multiply by 1000 and show as per mile |
| € (\u00A4) | Prefix or suffix | No |
Currency sign, replaced by currency symbol. If doubled, replaced by international currency symbol. If present in a pattern, the monetary decimal separator is used instead of the decimal separator. |
| ' | Prefix or suffix | No |
Used to quote special characters in a prefix or suffix, for example, "'#'#" formats 123 to "#123". To create a single quote itself, use two in a row: "# o''clock". |