Spectre Format Conventions

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

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.

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

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.

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

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.