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)" returns 2019-07 (Jul)
GMMMM	Gregorian month abbreviation—same as MMMM, but forces the use of the standard calendar Month name	October	`period("standard", "year-month", date("2019/07/04"))` format="GYYYY-GMM (GMMMM)" returns 2019-07 (July)
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—same as GMMM in offset year-month period parsing and formatting	Oct	`format_date(date("2015/10/24"), "MMM D, YYYY")` returns Oct 24, 2015
MMMM	Month name—same as GMMMM in offset year-month period parsing and formatting	October	`format_date(date("2015/10/24"), "MMMM")` returns October
MMMMM	First letter of month name	F	`format_date(date("2019/02/24"), "MMMMM")` returns F
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
ww	Week number	18	spectre eval "format_period(period(\"iso 8601\", \"year-week\", date(\"2016/05/05\")), \"YYYY.ww\")" prints 2016.18
YY	Two digit year	17	DIAL format—calc.format_date_range("1-27-55", "mm-dd-yy", 1950) returns 1955/01/27 Spectre format—format_date(date("2022/04/15"), "MM/DD/YY") returns 04/15/22
YYYY	Four digit year	2017	`format_period(period("standard", "year-month", date("2017/02/15")), "MMM-YYYY")` returns Feb-2017

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.

NOTES: 

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

• Starting with Spectre 7.1(6), the 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.

• Starting with Spectre 7.1(20) literal characters can be enclosed in quotation marks for date and datetime format strings. For example, with date 20210/05/10 and `format="\"Updated\" YYYY-MM-DD"`, the date prints as: Updated 2021-05-10.

• Starting with Spectre 7.1(21) the format string for dates can include custom null and unknown formatting.

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.

Starting with Spectre 7.1(21) the format string for datetimes can have up to two additional clauses.

<datetime format> [ ';' <null format> [ ';' <unknown format> ]]

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 instance "YYYY-\MMM-DD hh:mm:ss" parses "2018-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.

• Starting with Spectre 7.1(20) literal characters can be enclosed in quotation marks for date and datetime format strings. For example, with date 20210/05/10 and `format="\"Updated\" YYYY-MM-DD"`, the date prints as: Updated 2021-05-10.

The format string for numbers is:

<positive number format> [ ';' <negative number format> [ ';' <zero format> ] ]

The negative and zero formats are optional; if absent they use the positive format.

Starting with Spectre 7.1(21) the format string for numbers can have up to five clauses.

<positive/primary number format> [ ';' <negative number format> [ ';' <zero format> [ ';' <null format> [ ';' <unknown format> ]]]]

• An empty clause is treated as "default" and falls back to the primary clause

• An empty primary clause uses the default for the data type

• A clause that is just two quotes ("") formats to a blank string

• The format applies to integers, fixed100 and double data types

NOTE:

The default format for unknown values varies a little between programs. The Spectre command line displays unknown as "<unknown>". Other clients display it as blank, except if it is a dimension value in which case it is "<UNKNOWN>".

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
More examples with numbers:
€#,##0.00	Present the number as Euros.	-123.45 -€123.45
£000.00	Present the number as Pounds sterling.	-123.45 -£123.45
￥#####	Present the number as Japanese Yen.	-123.45 -￥123
$USD#,##0.00	Present the number as USD dollars.	-50.12 -$USD50.12
#.#,,m	Present the number in millions.	1200000 1.2m
###,k	Present the number in thousands.	256000 256k
#.###E0	Present the number with scientific notation.	0.00123 1.23E-3
00000.000E000	Present the number with scientific notation.	123.456 12345.600E-002
##.#%	Present the number as a percentage of 100.	0.4 40%
##.#'%'	Present the number as is with a percent symbol.	0.4 0.4%
Expanded format string for numbers available with version 7.1(21):
#,#;;"";n/a;!!!	Specify format for positive, zero, null and unknown values.	1234 1,234 -1234 -1,234 0 (blank) null n/a unknown !!!
"#,#;#,#;0;$null;!Error!	Specify format for positive, negative, zero, null and unknown values.	$null 1 1 a !Error! 0 0 1.00 1 1.111 1

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

• In Spectre number format strings, there are two ways to escape characters that would otherwise be interpreted as format tokens. You can put a backslash before each such character, or you can wrap them with quotes:

• format="\"I'm #\"#" displays 123 as "I'm #123"

The format string for Boolean data is:

<true format> [ ';' <false format> ]

The false format is optional.

Starting with Spectre 7.1(21) the format string for Boolean data can have up to four clauses.

<true format> [ ';' <false format> [ ';' <null format> [ ';' <unknown format> ]]]

For instance, the following Boolean format string:

Y;N;n/a;!!!

formats true as "Y", false as "N", null as "n/a", and unknown as "!!!".

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.
format="Yes (True);No (False);$null;!Error!"	Indicates the formats for true, false, null, and unknown data.

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.

Spectre has a particular way of dealing with duration when you are working with time values. A duration attribute can be used on tags that create columns or calcs:

• add in any input
• column declarations on text-input
• column-override in odbc-input
• dimension in cPlans
• calc in cPlans
• add in a Dive window
• column in a Dive window

Duration is a boolean with a default of false. If set to true, then that column must be a numeric column, and it will be marked as a duration column. Duration columns behave the same as normal numeric columns, except that they are meant to be displayed differently.

When duration columns are displayed they ignore any format string and display as h:mm:ss (the value is interpreted as seconds).

For example:

The following test data:

With a Dive like this:

dive {
  cplan {
    text-input "songs.txt" {
      column "Name" type="string"
      column "Duration" type="integer" duration=true
    }
  }
  window {
    dimension "Name"
    column "Duration"
  }
}

Outputs this result:

Spectre has the means to handle basic time zone data.

Inputs:

When using text-input for a Spectre Build, the format for a datetime column supports a zone element that looks like one of these:

• {zone}
• {zone:XXXX}

For inputs, use of one of these formats has the same effect—Spectre will expect a time zone to be part of the input. The time zone is a string of non-whitespace characters. For example: America/New_York. The values in the column are then recorded in Greenwich Mean Time (GMT) in the cBase. This is significant when using these columns in calculations.

If using the format {zone:XXXX}, XXXX can be one of the standard display formats such as short or long-generic.

A default time zone can be associated with the column for display purposes with the time-zone attribute. If unspecified, GMT is assumed.

Outputs:

When outputting such a column in a dive window, you can specify a time-zone attribute to apply to all the data rows.

If using a format string, you can use a zone tag to control the output using standard formats. Use of {zone} or {zone:} corresponds to SHORT_COMMONLY_USED

To access the other options, specify any of these after the colon:

• short
• long
• short-generic
• long-generic
• short-gmt
• long-gmt
• generic-location

Example:

For illustration purposes, consider this data:

And this sample Dive:

The results of the detail dive window are shown here::

Notice that the Los Angeles time of 7:19:47 AM is shown as relative to New York which is 3 hours ahead, 10:19:47.

Using the same test data with another Dive:

The results of the dive window are shown here:

Note that the one date in the winter month of January is listed as Eastern Standard Time (EST) , while the others are displayed as Eastern Daylight Time (EDT).

See also: Text, cBase, and Dive Input Build Tags.