Skip to main content
Pentaho Documentation

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".