Skip to main content

Pentaho+ documentation is moving!

The new product documentation portal is here. Check it out now at docs.hitachivantara.com

 

Hitachi Vantara Lumada and Pentaho Documentation

Common Formats

Parent article

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:

SymbolMeaningTypeExample
GEraText"GG" -> "AD"
yYearNumber

"yy" -> "03"

"yyyy" -> "2003"

MMonthText or Number

"M" -> "7"

"M" -> "12"

"MM" -> "07"

"MMM" -> "Jul"

"MMMM" -> "December"

dDay in monthNumber

"d" -> "3"

"dd" -> "03"

hHour(1-12, AM/PM)Number

"h" -> "3"

"hh" -> "03"

HHour (0-23)Number

"H" -> "15"

"HH" -> "15"

kHour (1-24)Number

"k" -> "3"

"kk" -> "03"

KHour (0-11, AM/PM)Number

"K" -> "15"

"KK" -> "15"

mMinuteNumber

"m" -> "7"

"m" -> "15"

"mm" -> "15"

sSecondNumber

"s" -> "15"

"ss" -> "15"

SMillisecond (0-999)Number"SSS" -> "007"
EDay in weekText

"EEE" -> "Tue"

"EEEE" -> "Tuesday"

DDay in year (1-365 or 1-364)Number

"D" -> "65"

"DDD" -> "065"

FDay of week in month (1-5)Number"F" -> "1"
wWeek in year (1-53)Number"w" -> "7"
WWeek in month (1-5)Number"W" -> "3"
aAM/PMText

"a" -> "AM"

"aa" -> "AM"

zTime zoneText

"z" -> "EST"

"zzz" -> EST"

"zzzz" -> Eastern Standard Time"

XTime zone offsetText"XXX" -> "-08:00"
'Escape for textDelimiter"hour'h" -> "hour 9"
''Single quoteLiteral"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:

SymbolLocationLocalized?Meaning
0NumberYesDigit
#NumberYesDigit, zero shows as absent
.NumberYesDecimal separator or monetary decimal separator
-NumberYesMinus sign
,NumberYesGrouping separator
ENumberYes

Separates mantissa and exponent in scientific notation.

Note: E does not need to be in quotation marks within a prefix or a suffix.

;Sub-pattern boundaryYesSeparated positive and negative sub-patterns
%Prefix or suffixYesMultiply by 100 and show as percentage
‰ (\u2030)Prefix or suffixYesMultiply by 1000 and show as per mile
€ (\u00A4)Prefix or suffixNoCurrency 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 suffixNoUsed 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.