Spectre Format Conventions

Token	Meaning	Example Value	Sample Usage
AAA	Abbreviated day of the week	Sun	`format_date(date("2015/10/25"), "AAA")` returns Sun
AAAA	Day of the week	Sunday	`format_date(date("2015/10/25"), "AAAA")` returns Sunday
D	One or two digit days in a standard calendar	4	`date("Mar 4, 2013", "MMM D, YYYY")` returns the date value 2013/03/04
D	Weekday number in an ISO calendar	2015-W53-5	`week_date(date("2016/01/01"))` returns 2015-W53-5
DD	Two digit day	09	`date("Mar 09, 2013", "MMM DD, YYYY")` returns the date value 2013/03/09
GM	Gregorian one or two digit month—same as M, but forces the use of the standard calendar for non-standard period types	2	`period("offset september", "year-month", date("2019/04/26"))` format="GYY-GM (GMMM)" returns 19-4 (Apr)
GMM	Gregorian two digit month—same as MM, but forces the use of the standard calendar	03	`period("offset march", "year-month", date("2019/03/15"))` format="GYYYY-GMM " returns 2019-03
GMMM	Gregorian month abbreviation—same as MMM, but forces the use of the standard calendar	Jul	`period("standard", "year-month", date("2019/07/04"))` format="GYYYY-GMM (GMMM)" 2019-07 (Jul)
GYY	Gregorian two digit year—same as YY, but forces the use of the standard calendar	19	`period("offset september", "year-month", date("2019/10/26"))` format="GYY-GMM (GMMM)" returns 19-10 (Oct)
GYYYY	Gregorian four digit year—same as YYYY, but forces the use of the standard calendar	2019	`period("offset september", "year-month", date("2019/12/04"))` format="GYYYY-GM (GMMM)" returns 2019-12 (Dec)
M	One or two digit month	2	`same_period(value("YearMo"), "2017-2", "YYYY-M")` returns the period with the same calendar and period type as YearMo, parsed from the string "2017-2" using the format "YYYY-M"
MM	Two digit month	03	`period("standard", "year-month", "03-2015", "MM-YYYY")` returns the period value 2015/03
MMM	Month abbreviation	Oct	`format_date(date("2015/10/24"), "MMM D, YYYY")` returns Oct 24, 2015
MMMM	Month name	October	`format_date(date("2015/10/24"), "MMMM")` returns October
Q	Quarter number	2	`period("standard", "year-quarter", "2017!2", "YYYY!Q")` returns the period value 2017 Q2
W	Week number	4	`format_period(period("iso 8601", "year-week", date("2016/01/31")), "YYYY!W")` returns 2016!4
YYYY	Four digit year	2017	`format_period(period("standard", "year-month", date("2017/02/15")), "MMM-YYYY")` returns Feb-2017

NOTE: If you want to include letters in the input or output date string, you must put a backslash ("\") in front of the characters. For example, "YYYY-\MMM-DD" parses "2020-M07-20".

TIP: The standard ISO date format is YYYY-MM-DD, while the DI standard date format used by the Data Integrator is YYYY/MM/DD. These formats are interchangeable when using functions in Integrator scripts.

NOTE: Starting with 7.1(6), the Spectre date parser can handle month names and abbreviations in any case (for example, "01-Jan-2019" and "01-jan-2019" are treated as the same value).

Token	Meaning	Example Value	Sample Usage
H	One or two digit hours	3	`format_timevalue(11160, "H:MM:SS")` returns"3:06:00"
HH	Two digit hours	13	`time_value("1306", "HHMM")` returns 47,160
M	One or two digit minutes	6	`time_value("600", "MSS")` returns 360
MM	Two digit minutes	45	`format_time("10:45", "hh:mm")` returns 10:45:00.
S	One or two digit seconds	3	`format_timevalue(3723, "H:M:S")` returns "1:2:3"
SS	Two digit seconds	1	`time_value("1:2:3", "SS:MM:HH")` returns 10,921

TIP: The DI standard time format used by the Data Integrator is HH:MM:SS.

The syntax for datetime parsing and formatting is "MM/DD/YYYY hh:mm:ss".

A datetime format string is a sequence of tokens, spaces and literal characters. Tokens are CASE-SENSITIVE. This is unlike date format strings, where for instance you can use either "yyyy" or "YYYY". This requirement is necessary to avoid ambiguous tokens (for example, does M mean month or minute) and introducing additional tokens.

NOTE:

• When parsing, a space matches any number of spaces in the input string.
• If you want to include letters in the input or output datetime string, you must put a backslash ("\") in front of the characters. For example, "YYYY-\MMM-DD hh:mm:ss" parses "2020-M07-20 15:07:00".
• If you put the format string in a Build script or cPlan calc, use double backslashes for letters (or in some cases quadruple backslashes).
• In addition to letters, other symbols that need backslashes are the left brace ("{') and the backslash itself.
• Other symbols, for example ":", "-", and "/", are treated as literals and do not need backslashes.

Format String	Description	Data -- > Result*
format ="#,#" format ="#,##0"	Round any decimal portion to present as whole number	9.0 9 12345.67890 12,346 0.678 1 -0.003 -0
format ="#,##0.00"	Round and present with 2 decimal places	9.0 9.00 12345.67890 12,345.68 -0.1368 -0.14
format="0,000.00"	Present as minimum 4 whole digits and round to 2 decimal digits	9.0 0,009.00 12345.67890 12,345.68 -0.1368 -0,000.14
format = "$#,##0.00"	Include a dollar sign and 2 decimal places	9.0 $9.00 12345.6789 $12,345.68 0.678 $0.68 -0.003 -$0.00
format="$#,#;\"$\"(#,#)"	Present positive number with leading dollar sign; present negative number with leading dollar sign and parentheses—accounting format	9.0 $9 12345.67890 $12,346 0.678 $1 -0.003 $(0) -12345.67890 $(12,346)
format="$#,#.00;\"$\"(#,#.00)"	Present positive number with leading dollar sign and cents; present negative number with leading dollar sign, cents, and parentheses—accounting format	9.0 $9.00 12345.67890 $12,345.68 0.678 $.68 -0.003 $(.00) -9.0 $(9.00)
format = "$#,###.00;-$#,###.00"	Present positive number with leading dollar sign and cents; present negative number with leading dollar sign and cents	9.0 $9.00 12345.67890 $12,345.68 0.678 $.68 -0.003 -$.00 -9.0 -$9.00
format = "#%"	Multiply by 100, round to whole number, and display with percent sign	9.0 900% 12345.67890 1234568% 0.678 68% -0.003 -0%
format = "#0.00\"%\""	Present the number with 2 decimal places and a percent sign	9.0 9.00% 12345.67890 12345.68% 0.678 0.68% -0.003 -0.00%
format = "$#,#k"	Present the number with the letter k and a dollar sign.	45 $45k 4500 $4500k 45000 $45000k 4500000 $4500000k
format = "$#,#,k"	Present the number as multiples of a thousand using a dollar sign.	45 $0k 4500 $5k 45000 $45k 4500000 $4500k

*NOTES:

• By default, Diver assigns a numeric of 2 decimal places to numeric cBase fields. If the formatting is set to None, the ProDiver output matches any Spectre formatting defined by the Build script or cPlan.
• The inclusion of thousands separators in Spectre output depends on the use of a locale that supports it, for example, en_US.

Format String	Description
format="True;False"	Indicates the format is the default for Boolean data: True or False.
format="T;F"	Indicates the data presentation is T or F.
format="Yes;No"	Indicates the data presentation is Yes or No.
format="true"	Indicates the data presentation is true or blank for false.

NOTE:

• In Spectre builds, format sets both the display and input properties, unless a separate input-format is specified.
• If the format has a blank after the semicolon, or if there is no semicolon, then blank values are considered false. Otherwise, when there is a semicolon and the string after it is not empty, blank values are parsed as null.