Many Spectre functions include options to indicate the format for input data or the format for output data. Certain syntactic conventions apply.
Dates and Periods
| 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.
Time
| 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.
Datetime
Starting with version 7.0(43), Spectre has a new syntax for datetime parsing and formatting. Now, the datetime syntax uses the following format ("MM/DD/YYYY hh:mm:ss") similar to the date syntax. The earlier %-style formatting ("%m/%d/%Y %H:%M:%S") is supported, but is deprecated. Use of the new style is recommended. Spectre automatically detects which style is used and prints a warning for Build scripts that use the earlier style.
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.
Date tokens
| Token (must be uppercase) | Meaning | Example Value | Sample Usage with input-format="YYYY/MM/DD hh:mm:ss" |
|---|---|---|---|
| Y | Year, full | 2018 | with 2018/01/15 13:30:00 and format="MMM D, Y h:mm" displays Jan 15, 2018 13:30 |
| YY | Year, 2 digits | 18 | |
| YYYY | Year, full | 2018 | with 2018/01/15 13:30:00 and format="MMM D, YYYY h:mm" displays Jan 15, 2018 13:30 |
| H | Half year (1 or 2) | 1 | |
| T | Tertile (1-3) | 1 | |
| Q | Quarter (1-4) | 2 | |
| M | Month, 1 or 2 digits | 1 | with 2018/01/15 13:30:00 and format format="M D, YYYY i:mm:ss pp" displays 1 15, 2018 1:30:00 PM |
| MM | Month, zero padded to 2 digits | 01 | with 2018/01/15 13:30:00 and format format="MM D, YYYY i:mm:ss pp" displays 01 15, 2018 1:30:00 PM |
| MMM | Month, 3-letter abbreviation | Jan | with 2018/01/15 13:30:00 and format format="MMM D, YYYY i:mm:ss pp" displays Jan 15, 2018 1:30:00 PM |
| MMMM | Month, full name | January | with 2018/01/15 13:30:00 and format format="MMMM D, YYYY i:mm:ss pp" displays January 15, 2018 1:30:00 PM |
| MMMMM | Month, 1-letter abbreviation (cannot be used for parsing) | J | with 2018/01/15 13:30:00 and format format="MMMMM D, YYYY i:mm:ss pp" displays J 15, 2018 1:30:00 PM |
| D | Day of month, 1 or 2 digits | 3 | with 2018/05/03 01:45:20 and format="MMM D, YYYY i:mm:ss pp" displays May 3, 2018 1:45:20 AM |
| DD | Day of month, zero-padded to 2 digits | 03 | with 2018/05/03 01:45:20 and format="MMM DD, YYYY i:mm:ss pp" displays May 03, 2018 1:45:20 AM |
| AAA | Day of week, 3-letter abbreviation | Mon | |
| AAAA | Day of week, full name | Monday | |
| AAAAA | Day of week, 1-letter abbreviation (cannot be used for parsing) | M |
NOTE: The modifier G can be placed in front of the Y or M, similar to the date format (for example, GYYYY-GMM). The purpose of that modifier is to force the use of the standard calendar for non-standard period types. Because datetimes do not use non-standard calendars, the modifier has no effect, but it is allowed.
Time tokens
| Token (must be lowercase) | Meaning | Example Value | Sample Usage with input-format="YYYY/MM/DD hh:mm:ss" |
|---|---|---|---|
| h | Hour (0-23), 1 or 2 digits | 3 | with 2018/04/17 03:10:30 and format="MMM D, YYYY h:mm" displays Apr 17, 2018 3:10 |
| hh | Hour (0-23), zero-padded to 2 digits | 15 | with 2018/01/15 15:30:00 and format="MMM D, YYYY hh:mm" displays Jan 15, 2018 15:30 |
| i | Hour (1-12), 1 or 2 digits | 3 | with 2018/04/17 03:10:30 and format="MMM D, YYYY i:mm pp" displays Apr 17, 2018 3:10 AM |
| ii | Hour (1-12), zero-padded to 2 digits | 01 | with 2018/01/15 13:30:00 and format="MMM D, YYYY ii:mm pp" displays Jan 15, 2018 01:30 PM |
| m | Minute (0-59), 1 or 2 digits | 6 | |
| mm | Minute (0-59), zero-padded to 2 digits | 08 | with 2018/01/15 13:08:00 and format="MMM D, YYYY i:mm pp" displays Jan 15, 2018 1:08 PM |
| s | Second (0-59), 1 or 2 digits | 3 | |
| ss | Second (0-59), zero-padded to 2 digits | 59 | with 2018/02/15 14:30:59 and format="MMM D, YYYY i:mm:ss ppp" displays Feb 15, 2018 2:30:59 PM |
| p | AM/PM marker ('A' or 'P'), 1 letter | A | with 2018/04/17 03:10:30 and format="MMM D, YYYY i:mm p" displays Apr 17, 2018 3:10 A |
| pp | AM/PM marker ('AM' or 'PM'), 2 letters | PM | with 2018/03/15 15:30:00 and format="MMM D, YYYY i:mm pp" displays Mar 15, 2018 3:30 PM |
| u | Milliseconds |
Time zones
| Time Zone Format | Example |
|---|---|
| {zone:short} | 2020/01/05 14:17:09 EST |
| {zone:long} | 2020/01/05 14:17:09 Eastern Standard Time |
| {zone:short-generic} | 2020/01/05 14:17:09 ET |
| {zone:long-generic} | 2020/01/05 14:17:09 Eastern Time |
| {zone:short-gmt} | 2020/01/05 14:17:09 -0500 |
| {zone:long-gmt} | 2020/01/05 14:17:09 GMT-05:00 |
| {zone:generic-location} | 2020/01/05 14:17:09 New York Time |
All time zone tokens must also include a corresponding time-zone property set to the time zone. All valid time zone values can be found here: ICU Time Zones.The datetime value is stored in GMT and displayed in GMT by default. Set the column format value to include the desired time zone for the displayed value. For more information, see Text, cBase, and Dive Input Build Tags.
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.
Numbers
| Format String |
Description |
Data -- > Result* | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|---|---|
|
format ="#,#" format ="#,##0" |
Round any decimal portion to present as whole number |
|
||||||||||
| format ="#,##0.00" | Round and present with 2 decimal places |
|
||||||||||
| format="0,000.00" |
Present as minimum 4 whole digits and round to 2 decimal digits |
|
||||||||||
| format = "$#,##0.00" | Include a dollar sign and 2 decimal places |
|
||||||||||
| format="$#,#;\"$\"(#,#)" | Present positive number with leading dollar sign; present negative number with leading dollar sign and parentheses—accounting format |
|
||||||||||
| format="$#,#.00;\"$\"(#,#.00)" | Present positive number with leading dollar sign and cents; present negative number with leading dollar sign, cents, and parentheses—accounting format |
|
||||||||||
| format = "$#,###.00;-$#,###.00" | Present positive number with leading dollar sign and cents; present negative number with leading dollar sign and cents |
|
||||||||||
| format = "#%" | Multiply by 100, round to whole number, and display with percent sign |
|
||||||||||
| format = "#0.00\"%\"" | Present the number with 2 decimal places and a percent sign |
|
||||||||||
| format = "$#,#k" | Present the number with the letter k and a dollar sign. |
|
||||||||||
| format = "$#,#,k" | Present the number as multiples of a thousand using a dollar sign. |
|
*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.
Booleans
| 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.
See also: Spectre Data Types