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.

Date format symbols are case-sensitive. For example, M is month and m is minute.

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.

The actual decimal separator and grouping separator depend on your locale and, in many steps, the Decimal symbol and Grouping symbol options.

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

To include literal text in a date pattern, wrap it in single quotes. To include a single quote, use two in a row.

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

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

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)

Number formatting can be affected by locale and by step settings such as Decimal symbol, Grouping symbol, and Currency symbol.

Date formats

Use the following symbols to define common date formats.

PDI date masks follow the same pattern symbols as Java date and time formatting.

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